Best Way To Update Existing Rows
Dec 6, 2006
IUpdating existing rows in a SQL server 2005 destination table
--I have a CSV file and every row needs to perform an update on a production table
The most straight forward is to use an OLEDB Command Data Flow Transformation Object that sends one SQL statement per row coming in. How do I configure this object ???
That might be okay if you've only got a small number of updates, however if you've got a large set, I prefer landing this set to a temporary table and then doing Set based updates in a following Execute SQL task. Can you please tell me how I can set up the Execute SQL Task to do set based updates in the Control flow??
thanks in advance
Dave
View 6 Replies
ADVERTISEMENT
Oct 1, 2015
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
View 4 Replies
View Related
Dec 12, 2014
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
View 5 Replies
View Related
Jul 25, 2007
Hi All,
I have the following Table
Type Name Value
x M1 5
x M2 10
x M3 20
y M1 10
y M2 15
y M3 30
Now, i need to add four more rows to the table
Type Name Value
x M1 5
x M2 10
x M3 20
y M1 10
y M2 15
y M3 35
z1 Total 15 (xM1+XM2)
z1 Diff 5 (xM3-xM1+XM2)
z2 Total 25 (yM1+yM2)
z2 Diff 10 (yM3-yM1+yM2)
Please help me.
Many Thanks,
View 2 Replies
View Related
Apr 28, 2006
I need to create a fairly simple package that copies data from one database using a sql querie and inserts it into another. what's the best way to handle rows that already exist in the destination table? The package needs to run once a day and ignore rows inserted from previous runs.
View 2 Replies
View Related
May 21, 2008
Hi all,
I am trying to insert some new rows into an existing SQL table. The table name is Agt_table, and I want to add some data for some new agents into existing columns:
Agent name, agent code, phone number, fax number
Example -
I want to add the following record to my existing table Agt_table
Agent name: ABC Company
Agent code: 012345
Phone #: 555-555-5555
Fax#: 555-555-5555
How would I write the sql statement to do that?
Thanks so much!!!
View 12 Replies
View Related
Mar 22, 2006
Hi,
I am using a DTS package to output a view to a pre-determined Excel file. Currently it just adds the output to the bottom of the current table in excel but i would like it to delete the contents of the worksheet before adding the new rows.
Any help is much appreciated.
Thanks
Greg
View 1 Replies
View Related
Apr 29, 2008
I know that this is legal sql: "SELECT 1 AS Blah"
I want to do something like this except for I need to select multiple rows each with a different value for Blah. The query needs to be legal to be passed to the SqlCommand.ExecuteReader Method. Is this possible?
View 3 Replies
View Related
Dec 24, 2007
Hey Guys
i need to add a datetime column to an exisitng table that has like 1.2 million records and its being accessed frequently
but i cant afford to stop the db at all
whenever i do : alter table mytable add Updated_date datetime
it just takes too long and i have to stop executing the query after a couple of mins
I am running sql express 2005 sp2. db size is over 3 gb but still under the 4 gb limit
can u plz advice on how to add this column. its urgent!!
thanks in advance
View 5 Replies
View Related
Mar 5, 2005
I have one table Phone and a table SmsMessage that are linked by the Cellnumber. Cellnumber is the primary key in Phone.
For some reason in the table Phone the Cellnumbers are stored with extra spaces like: '+27000000000 ', but in the SmsMessage table the same value is stored as '+27000000000'. However when I want perform an update to trim the Cellnumbers, I get the message 'Cannot modify values Cellnumber in Phone because there are dependent values in SmsMessage.
The fact is that there are no dependent values, but for MSSQL '+27000000000' and '+27000000000 ' is the same????! Note that the function Len(Cellnumber) gives me the length of the string WITHOUT the spaces as well.
Even if I remove all relationships from Phone, I still get the same error. Are there more places in MSSQL where relationships are stored besides the Diagrams?
Or is there a command that tells MSSQL to ignore all relationships for the next query?
Any ideas?
View 1 Replies
View Related
Jul 20, 2005
I have one table of new records (tableA) that may already exist intableB. I want to insert these records into tableB with insert if theydon't already exist, or update any existing ones with new data if theydo already exist. A column (Action) in tableA already tells me whetherthis is an INSERT, UPDATE, or DELETE. I'm able to derive that I can doan insert withselect * into tableB from tableA where Action = 'INSERT'....and I think I can handle the delete.But I'm stuck on the update. How do I do the update? An ordinaryUPDATE statement just won't do unless I use a cursor to cycle throughthe recordset. I want to avoid a cursor.
View 1 Replies
View Related
May 15, 2006
I need to create an SSIS package that updates columns in a table from columns in another database where the keys match. What's the best way to do this?
View 3 Replies
View Related
Nov 4, 2015
Existing table structure is below:
Table name: Student
Columns in Student are below:
Student_Id
Subject_Id
Quarter
Col1
Col2
Combination of Student_Id, Subject_Id and Quarter columns is the primary key. One student can take one subject in a quarter. Now the new requirement is a student can take multiple subjects in a quarter. So need to add another table like below:
NEW table name: Student_Subject and
column are below:
Student_id
Subject_Id
Quarter1
All the above three columns combination is primary key.
After the new table Student_Subject created,
remove Subject_Id column
from Student table.
When the user clicks on a button after selecting multiple subjects and provide col1 and col2 data then one row gets inserted into Student table and multiple rows gets inserted into Student_Subject table.
Is there any other table design that satisfies one student can take multiple subjects in a quarter?
View 15 Replies
View Related
Nov 21, 2006
Help,
I have a client that requres me to add a header line and trailer line to a flat file. The trick is the header and footer row is required to have a length of 120 with 5 colunmns while the data included in the query has a length of 1125 and about 70 columns. How can I append a header row to a file through SSIS.
View 1 Replies
View Related
May 21, 2002
I have 50 MSDE SQL2k servers, each server has around 10 customer databases.
There are 5 stored procecures need to update to 50*10 = 500 databases.
These 5 stored procedures each has many 'Go' keywords and 4 of 5 with more than 8000 characters.
What might be the best way to loop execute them automatically, instead of
isql/w to each database connection to run the script?
I had bumpped by 'Go' keywords error and limitation of max varchar of 8000 error.
thanks for the help
David
View 4 Replies
View Related
Feb 6, 2007
HelloI used to work in a Sybase database environment. When I had to insert/update records in the database, I always used "insert on existingupdate", in this way, you didn't have to check whether a recordalready existed (avoid errors) and you were always sure that afterrunning the scripts, the last version was in the database.Now I'm looking for the same functionality in MS SQL Server, asked afew people, but nobody knows about such an option.Does anybody here knows the SQL Server counterpart of "insert onexisting skip/update"? If this doesn't exist, this is a minus forMS ;).Greetz,Bart
View 4 Replies
View Related
Jul 2, 2007
Hi,
I'm building a package to import data from a flat file into a Customer table, and I have set up a Lookup to check if that customer already exists in this table, and if so, perform an Update command instead of the bulk load. I don't expect many updates, if any, this is why i just used the OLE DB Command instead of using a staging table.
I've a bit of a problem executing this within a transaction and having the lock table option set on the SQL Server Destination. Is there anyway I can get Transaction support for this Data Flow working, as I want to be able to rollback a complete file/import if possible.
Thanks
Trent
View 4 Replies
View Related
Jan 15, 2015
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
View 2 Replies
View Related
Feb 4, 2006
I am building a data warehouse. I have many columns I want to populate in a fact table using integration services. Sample fields are: companyID, companyName, companyNumberOfAccounts, company, NumberOfUsers.
In the integration services package, I would like to keep this fact table in place and populated with data and to build integration services packages that update each of the existing row's specific columns (e.g. companyNameOfAccounts) one by one. For example, I have a source of data that has companyID and companyNumberOfAccounts data.
Is it possible to use the SQL Server Destination or OLE DB Destination Integration Services elements to import companyID and companyNumberOfAccounts data so that existing records just have their companyNumberOfAccounts column updated?
View 1 Replies
View Related
May 12, 2004
I 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.
View 4 Replies
View Related
Mar 16, 2015
I am trying my first bulk update to an existing SWL table from a CSV text file,The text file naming is exacrtly the same as the SQL table, with the same attributes
The statements:
BULK INSERT [Jedox_prod].[dbo].[B_BP_Customer]
FROM 'c:Baanjedox_dailyjdcom4401.txt'
WITH
[code]....
The error message is:
[size=1Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (BP_Country).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".size=1]..The have checked and re-checked the BP_Country field ( the 1st field after the key) and I am not seeing any mismatches.
View 5 Replies
View Related
Jun 25, 2014
I need to have a script where it ask the user for a value, the script will search for all records that match the value. Then it will display the numbers of records found and ask the user to enter a different value. The rest of the script will use this new value and increment by 1 n times as the number of records found. I started the script where it will ask for "HANDLE" and display the number of records found with that "HANDLE"
declare @HANDLE as varchar(30)
declare @COUNT as varchar(10)
declare @STARTINV as varchar(20)
set @HANDLE = ?C --This is the parameter to search for records with this value
set @STARTINV = ?C --User will input the starting invoice number
SELECT COUNT as OrderCount FROM SHIPHIST
where HANDLE = @HANDLE
I just can't figure out how to proceed to use the entered invoice # and increment by 1 until it reach the number of records found.
This will be the end results:
Count=5 --results from query
STARTINV=00010 --Value entered by user
Handle,Inv_Num
AAABBB,00010
AAABBB,00011
AAABBB,00012
AAABBB,00013
AAABBB,00014
View 9 Replies
View Related
Mar 17, 2015
Have installed SQL Server 2008 R2 Express (includes SSMS tool) on Windows server 2008 R2 sp1 without any issues.Database created with no issues, full text catalog created via the wizard also with no issues but cannot run the process as a scheduled task of updating the catalog because the SQL agent is not available in the express version.
The full text index information is already being populated and updated by a third party application so this leaves just the catalog to be updated as and when new full text information is available.
I have a third party SQL scheduler which will run SQL scheduled tasks but requires a script to run the full text catalog update process
Is it possible to extract a script from the existing full text catalog to run the update process or how to create a script from scratch to do the same update catalog process in the third party scheduler?
View 1 Replies
View Related
Sep 4, 2006
Hi when i am using OLDB Command for Update Existing Records the Follwing ERror Code I am getting . Any one pls help me on this one.
1)[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command 1" (9282)
failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
2)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
3)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
View 1 Replies
View Related
Jun 4, 2007
Stepping thru the code with the debugger shows the dataset rows being deleted.
After executing the code, and getting to the page presentation. Then I stop the debug and start the
page creation process again ( Page_Load ). The database still has the original deleted dataset rows.
Adding rows works, then updating works fine, but deleting rows, does not seem to work.
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); }
/* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
View 1 Replies
View Related
Feb 15, 2005
i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.
any ideas on how i could do this easily?
thanks
View 1 Replies
View Related
Nov 14, 2007
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
View 1 Replies
View Related
Aug 13, 2007
Hi All,
I would like to restore database using RESTORE DATABASE ... REPLACE command.
If database exists already and has any open connections this command will fail.
I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command.
I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
Please advice me how to do it.
Thanks in advance,
Roman
View 3 Replies
View Related
Apr 22, 2008
Hi,
I have a table called Past with 4 columns(Number,WW,Goal,Eng) and in Number column has 53 rows, 1,2,3 - 53. Now i need to update the 53rd row with the following code and it works. Since i dont know how to combine this i update them separately.
Update Past
set WW = ('WW'+convert(varchar(10),datepart(wk,getdate())-1))
WHERE Number = '53'
Update Past
set Goal = (Select Eng_Goal from AverageEngTime)
WHERE Number = '53'
Update Past
set Eng = (SELECT ((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168) FROM AverageEngTime where Shifts = 'Average')
WHERE Number = '53'
Now, whenever i execute, I want my row 52 to be updated with data from row 53 and row 51 with data from row 52 and so on until row 1 with data from row 2. Meaning i just want to update new data for row 53 and other rows should be updated with the data from next line. How do i do that? Please help..Thanks
View 1 Replies
View Related
Mar 22, 2006
Hi,I'm using table it has 100 rows, how can i update 10 or 15 rows at thesame time with single query. In real case, if user enters "5" intextbox it means that the "5" rows will be updated.In SAS i was using 'outobs' {outobs = n} which means n rows will beaffected.Thanx.
View 7 Replies
View Related
Oct 29, 2006
Hello,I ended up with two identical rows in one table. They should have differences but I cannot update one, as it tries to update both of them, or throws an error. How to update only one row, and leave other as is?
View 10 Replies
View Related
May 13, 2004
Any idea how to fix my code. I am getting this error message below....
Server: Msg 512, Level 16, State 1, Procedure TrigRetReqRecIDP1, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Set @intRowCount = (select count(*) from RequestRecords where REID = @REID)
While (select @REID from RequestRecords) != @intRowCount
Begin
select @intRRID = (select REID from RequestRecords where REID=@REID and RRStatus = 'PE')
Exec TrigAssignImpTaskNewP1 @intRRID, @REID
End
View 2 Replies
View Related
Aug 24, 2007
Hi
I would like update the rows of table 1 with the values in table2 see below based on a matching ID. I don’t want to update the ID field as this would be a violation of the primary key I assume as ID is a primary key, just the FirstName and SecondName fields need updating.
Table1
(ID, FirstName, Surname)
Table 2
(ID, FirstName, Surname)
Any ideas?
Cheers
View 6 Replies
View Related
