I have a table which contains GUID and Name columns and I need to retrieve the last inserted rows so I can load it into table2.
But how would I find out the latest data in Table1. I seem to be lost at this I have read similar posts posing the same question but the answers don't seem to work for my situation.
I am using SQL Server 2008 and I upload my data using SSIS
1 - One way to do this is with triggers. Check out my blog entry that shows how to copy data from one table to another on a insert.
Triggers to replicate data = http://craftydba.com/?p=1995
However, like most things in life, there is overhead with triggers. If you are bulk loading a ton of data via SSIS, this can add up.
2 - Another way to do this is to add a modify date to your first table and modify your SSIS package.
ALTER TABLE [MyTable1] ADD [ModifyDate] [datetime] NULL DEFAULT GETDATE(); Next, change your SSIS package. In the control flow, add an execute SQL task. Insert data from [MyTable1] to [MyTable2] using TSQL.
INSERT INTO [MyTable2] SELECT * FROM [MyTable1] WHERE [ModifyDate] >= 'Start Date/Time Of Package'; Execute SQL Task = http://technet.microsoft.com/en-us/library/ms141003.aspx
This will be quicker than a data flow or execute OLEDB command since you are working with the data on the server.
