July 23, 2008 at 11:22 pm
Hi,
I have to design and develop SSIS package which can pull all the data from source table to destination table. But I need to extract only those records which modified/updated.
I can do it by using Merge Join or even with Lookup table. But My source table is having large number of records and it is taking more than 12 hours.
How can I do it using TimeStamp datatype?
Is there any other option to pull the updated records, keeping performance in mind?
Thanks in advance
July 24, 2008 at 12:57 pm
Hari
One thought would be to create a reference table that had the table name and the last timestamp value for each table. When an update or insert occurs to the table, SQL Server will place in a value into your timestamp column and you can then do a comparison between the reference table and the records in your source table for those records that have a greater timestamp in the source, just pull those records and then update the reference table.
You could do something similar with a datetime field but the difference being that you would need update your source system to update the last_modified_date column that you create for each table (or create a trigger or some other process that does this) whereas SQL Server handles the timestamp updates without any extra work from you with one catch.
If you insert a record in the source system, you must name all columns in statement to insert values(which is best practice anyway), you cannot just perform an "Insert into Table Values (1,2,3)", the query will not work because you have a new Timestamp column.
hth
July 24, 2008 at 10:58 pm
Hi Chuck Rivel,
Thank you for your help!!
I'll try this and I hope it will work.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply