October 5, 2011 at 8:53 am
Hi,
I have a requirement to update the values in a table thru SSIS, however, there is a primary key restriction. The table has a combination of 3 columns as a primary key. If the primary key already exists in a table (i.e. the combination of the values in all the 3 columns already exists), then I need to find out a way to update the other existing column in the table thru the Update statement.
But the problem is I dont know how to do this via SSIS. can someone pls help me in this regard.
Thanks,
Paul
October 5, 2011 at 12:25 pm
pwalter83 (10/5/2011)
Hi,I have a requirement to update the values in a table thru SSIS, however, there is a primary key restriction. The table has a combination of 3 columns as a primary key. If the primary key already exists in a table (i.e. the combination of the values in all the 3 columns already exists), then I need to find out a way to update the other existing column in the table thru the Update statement.
But the problem is I dont know how to do this via SSIS. can someone pls help me in this regard.
Thanks,
Paul
There must be some level of detail missing here. You say you need to update a table, but then it all becomes quite fuzzy. Are you trying to do something where it will insert a record unless the record you are inserting already exists? Some call this an upsert. In other words if your record exists, update it, otherwise insert it.
if exists(select * from table where primarykeycolumns = ???)
begin
Update table set columns = values
end
else
begin
Insert into table (columns) values (values)
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 1:59 am
Sean Lange (10/5/2011)
pwalter83 (10/5/2011)
Hi,I have a requirement to update the values in a table thru SSIS, however, there is a primary key restriction. The table has a combination of 3 columns as a primary key. If the primary key already exists in a table (i.e. the combination of the values in all the 3 columns already exists), then I need to find out a way to update the other existing column in the table thru the Update statement.
But the problem is I dont know how to do this via SSIS. can someone pls help me in this regard.
Thanks,
Paul
There must be some level of detail missing here. You say you need to update a table, but then it all becomes quite fuzzy. Are you trying to do something where it will insert a record unless the record you are inserting already exists? Some call this an upsert. In other words if your record exists, update it, otherwise insert it.
if exists(select * from table where primarykeycolumns = ???)
begin
Update table set columns = values
end
else
begin
Insert into table (columns) values (values)
end
Yes, thats exactly what I intend to do but I dont know how to apply your solution through SSIS...Would I need to create a temp table for this purpose ?
October 6, 2011 at 8:13 am
Since you are still in 2005 (which means no merge statement) you could do this in two steps.
Obviously the update is pretty simple because it will only update those it finds.
The insert is not too bad either.
something like this:
insert table
select Vals from Source
left join Destination on KeyFields
where Destination.KeyFields is null
Does that make sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2011 at 7:14 am
if exists(select 1 from table where primarykeycolumns = ???)
begin
Update table set columns = values
end
else
begin
Insert into table (columns) values (values)
end
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply