Incremental Load in a table using SQL query (No primary key or CDC option) RRS feed

  • Question

  • Hi, I have a scenario where I will have to load my table incrementally using a sql query. CDC not an option and no primary key in both source query and target table. Previously I was doing truncate table and simply reload using stored procedure. I want to reduce time and resources.

    I used MERGE statement and use combination of two filed in join (like T.c1 = S.c1 and T.c2 = s.C2) where C1 is binary data type and c2 is varchar. It didn't reduce my time.

    Merge TargetTable As T


    (               SELECT              c1,c2,c3,c4

                    FROM sometable

                    WHERE somecondition) s

                     on t.c1 = s.c1

                    and t.c2 = s.c2 ---(makes up unique condtion to check unique record)


      insert (c1,c2,c3,c4)

                      values( s.c1,c2,c3,c4)


     Note: I have c1 as varbinary and c2 as varchar. I used combination of both to identify uniquer reocord to match.  This solution works but didn't reduce time of execution. I want to reduce execution time

    Tuesday, June 18, 2019 7:25 PM

All replies

  • Hi Rosh,

    A few assumptions: both tables have no PKs, the server has enough resources, the binary data size is small per row, the table size in not a TB, and the table is local.

    Not having a PK is a rare thing to start off. Probably because of the binary data that need to be parted ways. This is a bad design, but then I suspect the join is inefficient...

    So, instead of relying on joins over binary data bulk load this table into a staging table with a PK by c1,c2 then use the same MERGE code.




    Tuesday, June 18, 2019 7:43 PM
  • Hi, can you please explain little more your solution with some example. I think loading everything in a staging table is duplicate effort, will have to run the query twice. Further can I get a composite key with varbinary + varchar datatype? what data type it would be then? would it be efficient in merger join. In terms of resources, I need to cut execution time, as this is DWH and already lots of jobs running. Currently with truncate it take around 30 minutes to populate my 2 tables. and same time it is taking in for Merge statement. Thanks.
    Tuesday, June 18, 2019 8:11 PM
  • Could you please answer the questions above?

    To begin:

    Loading a whole table should not take long unless it is very large and remote. Do not worry about the data type. A PK can be comprised of different data types. 




    Tuesday, June 18, 2019 9:01 PM
  • Hi RoshN,

    Please try to use Control Table Pattern, refer to Incremental Loading Data using Control Table Pattern - SSIS .

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Wednesday, June 19, 2019 9:45 AM
  • Hi Mona, could try , but I can't find Topic accompanying material. Still no luck with this.
    Wednesday, June 19, 2019 3:31 PM
  • Hi Rosh,

    The article in the link from Mona is what you need to read.




    Wednesday, June 19, 2019 6:43 PM
  • Thanks for reaching out. I have already gone through the article mentioned. At some point it says "To start the example, run the Control Table Example Creation.sql file in the Topic accompanying material ." I was looking for sample to create control table. Nothing found any other resource regarding creation for control table. So I was looking for is there any accompanying material with this topic, which I didn't find. Thanks

    Wednesday, June 19, 2019 8:09 PM
  • Perhaps you could find another tutorial online. The motto is to use this technique. But an incremental load would need a strong PK.




    Wednesday, June 19, 2019 8:26 PM