I have a list of 10000 websites that need to be updated with certain data.
So my question is would i be able to get SQL to lookup a certain list of websites? And then me update the required fields?
Its prob an easy answer, thanks!
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies View RelatedPardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
View 1 Replies View RelatedHi,
Having this sql=-10000 on this statement below, which referes to some mismatch between columns and host-vars.
This runs from MicroFocus program against SQLServer'05 table, all hosts are well defined, same picture.
Interesting thing is that if I change ws-volume for literal, eg.
B3M_VOLUME = 21 it will work fine, but doesn't work with B3M_VOLUME = :WS-VOLUME:
--------------------
EXEC SQL
SELECT *
INTO :DCLVB3M-BASE
FROM VB3M_BASE
WHERE B3M_REMSEQ = :WS-REMSEQ
AND B3M_LOCSEQ =
(SELECT B3M_LOCSEQ FROM VB3M_BASE
WHERE B3M_REMSEQ = :WS-REMSEQ
x2 AND B3M_VOLUME = :WS-VOLUME
x1 AND B3M_BUST <> :WS-BUST)
END-EXEC.
------------------
also anybody can point to good place to see list of all sql codes:
Tx all
dai
From Sql server point of view 390 tabels in database and 10000 transaction a day is it a lot ?
At what point performance starts slow down ?
I have written a package that archives off old orders over night, it appears that this package is failing after about 10000 second every time it is run. I don't think it is memory as I am running it and checking for memory leaks.
Basic run down of package is
EXEcute SQL task to get orders to delete
If a for loop, loop each ordernumber
within the for loop there are 2 dataflow
dataflow 1
find related records in child tables (oldb connection using query)
using a mutli split first
check (with lookup) for records already in archive database
only copy on a fail from the look up
second
delete related records
dataflow 2
do the same but for the parent table
SP1 CTP is installed on server.
Any ideas?
Hi allI just ranUPDATE dbo.tbl_forecastedSET update_ref = 0but it only updated the first 10000 records. I'm wondering if anyone cantell me why this is, and can I get around it.I have looked on google and found a few references, but nothing in too muchdetailsthanks in advanceAndy
View 2 Replies View Related
I would like to select a BIGINT type and get a formatted result with commas. Anyone have ideas?
declare @i bigint
set @i = 123456789
select @i
--Would like to get
123,456,789
How to enable the SQL to update all fields?
"UPDATE addresses SET "
+ "strCompany='" + strCompany + "',"
+ "Name='" + strName + "',"
+ "strAddress='" + strAddress + "',"
+ " strPhone='" + fields.phone.getText() +
"',"
+ " strHp='" + fields.hp.getText() + "',"
+ " strFax='" + fields.fax.getText() +
"',"
+ " strEmail='" + strEmail + "',"
+ " strStart='" +
fields.start.getText().trim() +
"',"
+ " intDay= '" + Integer.valueOf(dd) +
"',"
+ " intMonth='" + Integer.valueOf(mm) +
"',"
+ " intYear='" + Integer.valueOf(yy) +
"',"
+ " strMrc='" + fields.mrc.getText() +
"',"
+ " strIsp='" + fields.isp.getText() +
"',"
+ " strDes='" + fields.des.getText() +
"',"
+ " strSale='" + fields.sale.getText() +
"',"
+ " strContract='" +
fields.contract.getText() +
"',"
+ " strMark='" + fields.mark.getText() +
"'"
+ " WHERE strCompany='" + strCompany
+ "'"
+ "AND strName='" + strName + "'";
Hi,
I would like to delete a data from a 750million row table in chunks of 10000,without blocking the users.As ours is a 24/7 shop I donot want to block the users for a long time.
Answer for this is highly appreciated.
Thanks
Samna
Is there any Best Practice, 3rd Party Tool for Automating SQL Server Patching for around 10000+ SQL instances.
It is very difficult to do the same manually because each server will consume atleast 1-2 hours and if this is completely done manually then n number of people might be required for accomplishing this task which doesn't looks feasible at all.
I want to Insert 10000 rows at a time and commit in sql server , Isthere a way to do it if the source tables have no id fields ?What would be the most efficient method?ThanksAjay
View 1 Replies View RelatedHi all,I asked a similar question a few weeks ago but I didn't explain myself to well so I was hoping for some more input.I have created a webpage in Visual web developer that contains a gridview that points to an SQL table.All works well as far as searching the table and sorting. The SQL table itself is Droped ( deleted ) and then re-created every 1 minute from a query to a linked DB2 database so that we have a "live" table of our DB2 system but in SQL.....However,There is now a requirement to be able to ADD data to this SQL table. eg: a comments field.. from the website.My procedure for re-creating the SQL DB is now useless as any data added through the website would be lost when the table gets re-created.What is the best way for me to achieve a permanent table in SQL that gets updated from the Linked server DB2 but that I can also add comments from the front end (Website Gridview)..I'm assuming some sort of update query and a join to a seperate, permanent table that contains the comments?.Please help. Ray..
View 1 Replies View RelatedI'm not a T-SQL expert so I'm calling on the pros. I want to design a stored procedure that updates only fields in a given record for which a non-null parameter is specified.
Example:
Create dbo.MySproc
@CustomerID int,
@CustomerFirstName varchar(50)=NULL,
@CustomerLastName(50)=NULL
AS
UPDATE Customers
SET
CustomerFirstName= @CustomerFirstName , //update only if non-null
CustomerLastName= @CustomerLastName //update only if non-null
WHERE CustomerID = @CustomerID
RETURN
So say when calling sproc if @CustomerFirstName is specified but @CustomerLastName is not, the field CustomerFirstName would be updated but field CustomerLastName would retain original data.
Thanx in advance!
I have a am doing some date calcs () . The situation is that I have a Move date (date a customer moved to a new home). I want to calculate their sales for the following 0-3 months after the move (month 0 being the move month). I have the month and year of the move (MthStart, YrStart), and I am adding 3 to MthStart to get the MthEnd of that 0-3 month period. I will then find sales BETWEEN YrStart&MthStart AND YrEnd&MthEnd (there is a YrMth field in the sales table)
Of course, for MthStarts 10, 11, and 12, the ends are 13, 14, and 15. So for these, I need to subtract 12, and increment the YrEnd by 1.
I am wondering if there is a way to update both the MthEnd and YrEnd fields at one time instead of separate SETs (or maybe I am just thinking about this the hard way to begin with). Is there a way to update both in a single CASE statement like WHEN MthEnd> 12 THEN MthEnd-12 AND YrEnd+1?
Code:
CREATE TABLE #myTable (
YrStart INT,
MthStart INT,
YrEnd INT,
MthEnd INT);
[code]....
I hava at large database and I need to search all tables for the string 'NaN' and replace it to null to enable conversion.
Is there any way to do this so that i do not need to write an update query for every column?
Would appretiate an example if anyone knows how to manage this.
Thanks!
Hi All,In Oracle, I can easily make this query :UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'I cannot seem to be able to do the same thing with MS-SQL. There areonly 2 ways I've figured out, and I fear performance cost in both cases,which are these :1)UPDATE t1 SET f1=(SELECT AVG(f3)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'and then the same statement but with f2, and2)UPDATE t1 SET f1=(SELECT AVG(f3)FROM t2WHERE t2.f5=t1.f6),f2=(SELECT SUM(f4)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'Is there a way with MS-SQL to do the Oracle equivalent in this case ?Thanks,Michel
View 3 Replies View Related
Hi,
I have a table with eight (8) fields, including the primary key (rfpid). Three of the fields are foreign keys, which take their values form lookup tables. They are int fields (pmid, sectorid, officeid). One of the fields in this table is based on putting together the descriptive field in the lookup table for sector (tblsector). The two other fields to be part of this string are the rfpname and rfpid. This creates the following string:
rfpsector_nameproposalscurrent_year
fpname_08_rfpid
NOTE:
The words rfp, proposals are words that have to be part of string;
the slashes are to also appear.
current_year would be defaulting to datepart = year (2008)
The part that has the last two digits of the current year then the underscore and then the rfpid should be connected by an underscore to the rfpname.
I am at a loss and would greatly appreciate any help.
Thanks
I have some tables with 1:n-relations. Now I want to update the n-side of the relation. Whatever i do the result is always:
Subquery returned more than 1 value. This is not permitted ...
Both number and internalNumber are unique in table k, which is the 1-side of the relation. Ab is one of the n-side tables.
UPDATE Ab
SET
knr = (select k.internalNumber from Kunden as k where kNumber=k.number)
In my opinion there should be one kNumber at a time and for each
kNumber exists exactly 1 record in k. The following gives the same
result:
UPDATE Ab
SET
knr = k.internalNumber from Kunden as k where kNumber=k.number
Any suggestions? Thanks in advance
Thomas
Hi,
I am using SSRS to generate a 4 page statement... So i have a plan whose no. of Participants is 3271 rows... so While we try to export them to a PDF we will have around 13084 pages... but we cannot export so many pages at one time (it craps out). so right now we are breaking it up by start part and stop part and creating a pdf..
Is there a way that SSRS can handle so many pages at one go without any problems... if so how ??
Regards
Karen
Hello all
I am trying to import an XLS to a table in my database. Sounds simple, but the import should be an UPDATE, not an INSERT. In other words, I want to transfer data from the XLS sheet, to specific field in my database.
Let me explain more clearly.
I have an XLS sheet with a manager ID and a employee ID. The table in the database is already populated with all employee ID's but the field manager ID is still empty. Every employee has a different manager.
The import should place the manager ID's in the fields where the employee ID is the same as in the XLS sheet.
If somebody could please help me out, thank you!
Best regards
Maarten
Hi guys, I have made several Access-based CMSs but now I am using SQL Server. I can read the records but my first attempts at writing are resulting in new records (with new ID) but all the fields are null.
I am posting the data from a form to the same page and an if /then statement catches the flag in the URL and runs the update script below. All the field names are correct.
if request.QueryString("add")<> "" then
Dim rsUpdateEntry
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
rsUpdateEntry.Open "SELECT * from generic_country_info" , oConn, 2, 3
rsUpdateEntry.AddNew
rsUpdateEntry.Fields("title1") = Request.Form("title1")
rsUpdateEntry.Fields("body1") = Request.Form("body1")
rsUpdateEntry.Fields("title2") = Request.Form("title2")
rsUpdateEntry.Fields("body2") = Request.Form("body2")
rsUpdateEntry.Fields("title3") = Request.Form("title3")
rsUpdateEntry.Fields("body3") = Request.Form("body3")
rsUpdateEntry.Fields("title4") = Request.Form("title4")
rsUpdateEntry.Fields("body4") = Request.Form("body4")
rsUpdateEntry.Fields("title5") = Request.Form("title5")
rsUpdateEntry.Fields("body5") = Request.Form("body5")
rsUpdateEntry.Fields("image1") = Request.Form("attach1")
rsUpdateEntry.Fields("image2") = Request.Form("attach2")
rsUpdateEntry.Fields("image3") = Request.Form("attach3")
rsUpdateEntry.Fields("image4") = Request.Form("attach4")
rsUpdateEntry.Fields("image5") = Request.Form("attach5")
rsUpdateEntry.Fields("country") = Request.Form("country")
rsUpdateEntry.Fields("dest_url") = Request.Form("dest_url")
rsUpdateEntry.Update
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
end if
Thanks
Mark
I am attempting to update a table that drives a report. The report is at the order level. An order can have several types of demand (revenue $): Ship and/or Backordered. When I update one of these demand fields on the report table, I would expect those orders that have that type of demand to update, and those orders that don't have that type of demand to remain at their current value ($0 in this example which is the table default). But what is happening is that orders that don't have that type of demand are changing to NULL. What am i doing wrong with my update statement that is causing this?
Code:
CREATE TABLE #sales(
OrdNo int,
StatusGrp varchar(10),
Demand decimal(10,2)
)
INSERT INTO #sales
VALUES (1,'Ship',100.00)
[Code] .....
Create Table #Temp (ID int not null primary Key, Name varchar(25) not null, DOB datetime not null, Sex char(1), Race char(1), Height int, Weight int)
insert #Temp
select 1, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', NULL,NULL, NULL, NULL
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223
[Code] .....
/* Expecting Output */
select 1 as ID, 'Kenneth' as Name, '1963-02-26 00:00:00.000' as DOB, 'M' as Sex,'C' as Race, 516 as Height, 160 as Weight
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223
union
[Code] .....
I have a field where all of the data is 5 characters in length. The last character denotes a status and will always be an F, H, or T. I want to add a new field (which I will do manually) and populate the new field with the last character from the "old" field. Once that is complete, I want to eliminate that 5th character from the old field.
OLD FIELDNEW FIELD
B123F
B123H
B123T
OLD FIELDNEW FIELD
B123F
B123H
B123T
"David Portas" <snipped for brevity> wrote:Example 1:[color=blue]>> UPDATE table_a> SET col = ? /* Unspecified */> WHERE EXISTS> (SELECT *> FROM table_b> WHERE table_b.key_col = table_a.key_col)>[/color]<snip again>Many thanks. I have used this sample extensively since you posted it. Hopeyou (or someone) can help me with one more thing: How would it be writtento update several fields in table A with data from table B, where as youhave shown, a column matches the records?TIA!~ Duane Phillips.
View 4 Replies View RelatedI have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
Please help, this is getting really confusing.
I need creating an update trigger that involved two tables and a few fields.
tblCases
Fields
Defendent1
Defendent2
Defendant3
tblCaseBillingDetails
Fields
DefCount
I would like to create the trigger on tblCaseBillingDetails so that when the data in the Defendant fields are updated, the trigger fires and updates the Defendant count DefCount.
I've just written a query that successfully brings back the data from one table based on the information from another. Basically we have been given a table of information and need to update certain fields in our user_group table with the new info.
Here is the SELECT statement
SELECT user_group.id, user_group.name, user_group.description, Consultants.description AS Expr10, user_group.btype, user_group.rootmenu,
user_group.intra_user, user_group.primary_g_id, user_group.fname, user_group.lname, user_group.ntlogon, user_group.lang_id,
user_group.[external], user_group.title, user_group.work_tel, user_group.work_fax, user_group.work_ext, user_group.mobile, user_group.sex,
user_group.add2, user_group.add3, user_group.town, user_group.county, user_group.pcode, user_group.private_flag,
[code]....
We want to update the 'description' on the user_group table with the 'description' from the 'consultants' table. To test this, we only want to write the UPDATE so that it changes the description where the name is 'Adam Froth. The UPDATE statement that we've written is
UPDATE user_group
SET user_group.description = Consultants.description
FROM user_group
INNER JOIN Consultants
ON user_group.description = consultants.description
WHERE name like 'Adam Froth%'
but it keeps erroring and saying that it could 'Not be bound'.
I have the following store procedure which execute perfectly fine Under SSMS as it is :
-- Insert statements for procedure here
SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
,PIT.ProductItemCode
[Code] ....
But then when I try to create a dataset using ReportBuilder and pointing to that StoreProcedure, I get the following error message box :
"Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct. An item with same key already been added" ...
question need help
how can i use this code below not for insert
i need it for update another table but only if exist
the link to the code in this FORUM
http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=3208536&SiteID=1&Quote=True
Adam Haines wrote:
GPS,
Since you have such a dependency on dates and date calculations, you should implement a calendar table. A calendar table will make calculation such as this much more simplistic.
Calendar table link http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.
Note the calendar table I use is a little different than this one. The only thing you will need to change is isodate to dt.
Now the code to get the results you need:
Code Snippet
declare @t table(
id int,
fname char(4),
Start_Date datetime,
End_Date datetime,
val_holiday int
)
insert into @t values (111, 'aaaa', '3/15/2008', '03/21/2008', 1 )
insert into @t values (222, 'bbbb', '05/2/2008', '05/9/2008', 3)
insert into @t values (333, 'cccc', '04/3/2008', '05/15/2008', 4)
insert into @t values (333, 'cccc', '04/29/2008', '07/07/2008', 1 )
select id, fname, cal.ISODate, val_holiday
from @t t1
inner join Calendar cal
on cal.isodate >= t1.start_date and
cal.ISODate <= t1.end_date
is possible to do it
TNX
Help -
I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
rb