Data Driven Query Task

Question

Post reply

Data Driven Query Task

mdriscoll

SSC Enthusiast

Points: 166
More actions
October 18, 2002 at 2:39 pm

#158210

Hi Again:
Heres what I want to do:
Compare records from 2 tables and get a set of different records, then update records in the second table with the resulting .
The Data Driven Query Task seems like it would work great - but I dont need the transformations, and it wont let me out without selecting a table from a different database? (both my tables are in the same database))
Any Ideas??
Mike

Viewing 6 posts - 1 through 5 (of 5 total)

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

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 1

You could do this with a piece of SQL

i.e.

UPDATE t2

SET t2.col1 = t1.col1

FROM table2 t2

JOIN table1 t1 on t2.pk = t1.pk

WHERE t1.col1 <> t2.col1

You will need to use ISNULL to handle comparision of NULL values or use the SET ANSI_NULLS statement.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

mdriscoll SSC Enthusiast Points: 166 More actions · Answer 2

Thanks, That will work great for the updating. Here's the last part- I also need to write to a log table for every update. How can I insert a row of log data in a seperate table for every update that happens?

Thanks,

Mike

NPeeters SSChampion Points: 12558 More actions · Answer 3

That depends on what you want to do exactly. The easiest and most transparent way of achieving this is by setting a trigger on the second table that is fired automatically when an update occurs on that table.

The problem might be that other apps also update data in that table, triggering the execution of the trigger too. You'll have to check that.

mdriscoll SSC Enthusiast Points: 166 More actions · Answer 4

Yes, other apps do update this table. What I need to do sounds very simple.

1. Grab a set of records from table1 that don't match table2. (the inconsistancies)

2. Update table2 with the table1 recordset. (make the tables match)

3. insert a log record in table3 for every update that happened.

why is this hurting my brain - should it be this hard?

Chiefley SSC Journeyman Points: 79 More actions · Answer 5

I suggest you switch the order of things to this:

1) Insert a log record in table3 for every record in table1 that doesnt match table2, using an Insert...Select construct where the select part finds the offending records in table1. What you are inserting into table3 are the keys of the offending records in table1.

2) Then, update all the table2 records with the table1 records whose keys are in table3.

Doing it this way, you dont need a temporary table to hold the offending table1 records, and you also are left with a log (table3) of what got updated.

Something like this:

Insert Into table3 (table1PK)

Select t1.pk From table1 t1

Join table2 t2 On t2.pk = t1.pk

Where t1.col1 <> t2.col1

Or t1.col2 <> t2.col2

o

Or t1.coln <> t2.coln

Now you have a table3 full of the PKs of table1 records that differ from table2's records. Use the PKs in table3 to run an update from table1 to table2 by joining to table3.

Dudley