INSERT+UPDATE

Question

Post reply

INSERT+UPDATE

day-476284

SSCommitted

Points: 1844
More actions
August 24, 2008 at 10:58 pm

#197472

Hi,
I need to update SRV2.DB2.table2 reading the last updated records from SRV1.DB1.table1.
I tried to methods:
1) Execute SQL
2) Data Flow
The main problem is I have to store the last updated ID s from SRV1.DB1.table1 to temporary result set which I can use to update every matching ID s in SRV2.DB2.table2.
Two tables are using different connection.
method1:
I can create 2 execute SQL.
First Query:
SELECT ID FROM SRV1.DB1.table1 WHERE TYPE = 'C' (Put them into object variable User::ResultSet)
Second Query:
UPDATE SRV2.DB2.table2 SET TYPE = 'C' WHERE ID IN (SELECT SELECT ID FROM User::ResultSet)
QUESTION:How can I read the ID s from User::ResultSet?
method2:
OLE DB Source1: SELECT ID FROM SRV1.DB1.table1 WHERE TYPE = 'C'
OLE DB Source2: SELECT * FROM SRV2.DB2.table2
MERGE JOIN (Find matching IDs)
OLE DB Command:
UPDATE SRV2.DB2.table2 SET TYPE = 'C' for matching IDs
OUESTION: How can I UPDATE only matching IDs

Viewing 8 posts - 1 through 7 (of 7 total)

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

day-476284 SSCommitted Points: 1844 More actions · Answer 1

Hi,

OK, I found the solution:

1.Store the result to an object variable from table1

2.Use Foreach Loop Container and set object variable as ADO object source variable and map a new variable for IDs to be able read one at a time

3.Inside the Foreach Loop Container run Execute SQL Task to update Table 2 and map the new variable from the Foreach Loop Container in the WHERE clause

Thanks for everyone.

matty_p75 SSCommitted Points: 1708 More actions · Answer 2

day,

i'm trying to do an update after doing a merge join. Surely the method you have described updates one row at a time? I've got millions of rows so this surely isn't the best way to do this?

If anyone has any thoughts on how to do an update (post merge join) can they let me know please (and preferably something that is efficient when working with millions of rows).

thanks,

Matt

day-476284 SSCommitted Points: 1844 More actions · Answer 3

Hi Matt,

I resolved the problem using merge join.

There is a tutorial:

http://www.mssqltips.com/tip.asp?tip=1322

In order to use merge join, you have to sort the fields you are trying to match.

Can you give me specific criteria, what exactly you are trying to match, so I can be more helpful.

day-476284 SSCommitted Points: 1844 More actions · Answer 4

Hi Matt,

This is also useful tutorial:

Get all from Table A that isn't in Table B

http://www.sqlis.com/311.aspx

matty_p75 SSCommitted Points: 1708 More actions · Answer 5

Thanks for the links. It's always useful to build up examples and best practices. I have actually merged using the first technique - pre sorting as the data is taken from an OLE DB source (using a query/command rather than just referencing a table). What i actually can't find anywhere is how to actuall update existing columns in the table based on the merge. I want to update a column to 0 on one table where the merge condition is met. I have found that i can add a derived column task and replace data, but this only replaces it in the data flow as far as i understand (i could be wrong on this). So how do you update data in a table based on the merge? The way i see it is like having a disconnected recordset in vb6. And in that case you reconnect and update the source table, but how do i do this here?

If you have any ideas let me know,

Matt

day-476284 SSCommitted Points: 1844 More actions · Answer 6

Hi Matt,

This is what I usually do:

1)Control Flow>Toolbox>Data Flow Task

2)

Data Flow>Toolbox>Ole DB Source1 (Connection, SQL Command from Table1)> Toolbox> Sort1 (Select the column (Table1ID) you want a match, select all columns in Pass Through)

Data Flow>Toolbox>Ole DB Source2 (Connection, SQL Command from Table2)> Toolbox> Sort2 (Select the column (Table2ID) you want a match> Output Alias = NullIDs)

Toolbox > Merge Join

(Pull green arrow from both Sort1 and Sort2 and tag to the Merge Join)

(Double click on Merge Join> Join Type "inner join">connect column that you want to match)

3)Toolbox > Conditional Split > Output Name = "MatchingResults" Condition = ISNULL(NullIDs)

(Pull green arrow from Merge Join and tag to the Conditional Split )

4)Toolbox > OLE DB Command > UPDATE dbo.Table2 SET ColumnYouWantToUpdate= 'Value' WHERE Table2ID = ?

(Column Mapping Table1ID = Param_0)

I hope it helps,

Cheers,

/Day

day-476284 SSCommitted Points: 1844 More actions · Answer 7

OOPS,

Sorry Matt

Please ignore Step3

It is an inner join you don't need unmatching condition this time.