Compare two tables and insert/ update

Question

Compare two tables and insert/ update

sqlstar2011

SSCertifiable

Points: 6000
More actions
April 7, 2009 at 3:07 pm

#132910

I have table A and table B. What I would like to do is to update each row in table B if the key value for that row in table B has a match in table A.
so if table B has following record:
City State
Raleigh NC
Durham NC
and table A has following record:
City State
Raleigh SD
Durham NC
assuming that City is the key column in both tables, after the update table B would look like this:
City State
Raleigh SD
Durham NC
How can I accomplish this SSIS, given that table B exists in an oracle db and table A in a sql db. can this be done using script task? and is there an example that I can look at?
thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

You can use a Lookup Transformation to look up the values in the SQL Server table, matching the City column from the incoming data from the Oracle database to the column in your SQL Server table on the column of the same name. Use the matched output from the Lookup transformation and run it through a Script Component (configure it as either a transformation or a destination) and update each row in your SQL Server table.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

sqlstar2011 SSCertifiable Points: 6000 More actions · Answer 2

Tim

Thanks for responding.. can you give me a little more detail on how would I accomplish this in script component.

thanks