'How to do incremental load in SQL server

I have DB tables where there are no identity column. We have client data fetched from DB2 to SQL Server and unfortunately DB2 design doesn't have identity columns.

Now we have some data inserted, updated and deleted from source (DB2/SQL Server) and these data I want to load to destination (SQL Server) using some incremental load concept.

I tried SSIS lookups in Dataflow task however it's taking huge time to simply insert one new record. Please note that, in "lookup transformation editor" I'm mapping all "available input columns" to available "available lookup columns " as there is no identity column. I think, this is why it's taking time. I have few tables having around 20 million records.

Is there any faster method /ways available to do this, specially when table does not have identity column? Is except or SQL merge will help?

I'm open to have any other approach other than SSIS.

sql-serversql-server-2008ssis


Solution 1:[1]

Look up is SSIS takes some time, so you can use ESQL Task and call the merge procedures. I think what you can do is use merge procedures there you can create a column in your source table and update the records in the column like 

merge desination
using 
 {
 source columns from source s}
 join desination d
 on s.primarykey=d.primary key
when matched then
 s.updatedrecord=1
 when not matched then
 insert into desination columns.

from the above the query you new records will be inserted and the updated records with the help of updatedrecord column you can update or insert them in your destination table successfully.

you can go to the following link for merge procedures.

https://www.sqlservercentral.com/Forums/Topic1042053-392-1.aspx https://msdn.microsoft.com/en-us/library/bb510625.aspx

Solution 2:[2]

If your source is a SQL query from DB2 for instance, try adding a new column to this. It will be a checksum value over the columns you select "expect to change or want to monitor changes over".

SELECT 
BINARY_CHECKSUM(
    Column1
    ,Column2
    ,Column3)AS ChecksumValue
,Column1
,Column2
,Column3
FROM #TEMP

You would have to add this to your existing table in SQL as well to be able to start comparing. If you have this, then you can do the lookup on the checksum value rater than on the columns. Since number lookups are a lot quicker than varchar comparisons over multiple columns. I am guessing since there is no key, you would then have to split the data between checksum matches (which should be no change existing records) and non matches. The non matches Could be new rows or just updates. But your set should be smaller to work with.

Good luck. HTH

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rohit Gupta
Solution 2 Inus C

Related Questions