loop for insert into

Hello,

I have 10 tables (T1Orj,T2Orj,…T10Orj) and I need to find modified rows from each table and insert them to T1Bak, T2Bak, …T10Bak. Although original and bak tables have the common fields , the original tables have more fields than bak tables, and T1Orj, T2Orj, … T10Orj tables have different table structures.

I can go head and write a insert into query for each table, I am just wondering Is there any way I can do this in a loop for all tables?

You can use EXEC to ececute a dynamically constructed SQL statement that would run your query on every table from a list.|||That sounds to me like a job for a trigger, inserting the old data into a backup table at the time the delete / update is done. Otherwise if this is only a single execution job you should go the way the other poster mentioned, although this is based on dynamic SQL which should be avoided in this cases.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Not really. You can use information_schema.columns to generate these statements, but there is no real easy way to do it other than actual compiled statement.

What purpose is this? If you want to do this offline (and not with a trigger as also suggested), the easiest way to do this would be to add a rowversion(timestamp) column to the Orj tables and a binary(8) column to the bak tables (to hold the version of the timestamp in the bak table)

Then the statements would be:

--new rows
insert into <bak> (<bakColumns>)
select <orjColumnsThatBakHas>
from <orj>
where not exists (select *
from <bak>
where <orj>.key = <bak>.key)

--changed rows
update <bak>
set <bak>.col1 = <orj>.col1,
<bak>.col2 = <orj>.col2,
...
<bak>.colN = <orj>.colN,
from <bak>
join <orj>
on <org>.key = <bak>.key
where orj.rowversion <> <bak>.rowversionCopy
--or compare all columns if rowversion not a posibility

I also am guessing you don't care about deletes, but if you want to delete rows:

delete from <bak> (<bakColumns>)
where not exists (select *
from <orj>
where <orj>.key = <bak>.key)