Forum Discussion

gnanny's avatar
gnanny
Copper Contributor
Mar 30, 2023

Bulk Insert causing duplicates in target table

We use ETL tool(C# based) which uses Bulkcopy method to do bulk insert to target table.

Recently we are facing  2 Issues in our daily loads

1. Target table with no primary key 

- the load returns success but it inserts 5x times of the same record. It loads same records 5 times.

 

2. Target table with Primary Key 

- The load returns failure with primary key constraint  but it inserts the rows into target table.

 

DBA Team is unable to find anything in there logs ,So I am confused why this error occurs.

 

This occurs daily  in 10% of the jobs and once you rerun after 2-3 hrs the same job finishes successfully without above mentioned issues.

 

Please suggest how can I debug this issue.

 

BULK INSERT
etl
SQL Server 2016
SQLServerAlwaysOn
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Mar 31, 2023

     

    1. Target table with no primary key 

    that's already the main failure.

     

    It loads same records 5 times.

    You didn't provide any detail about your ETL process/tool, source data etc., so how could we guess?

     

    DBA Team is unable to find anything in there logs

    Of course not, why should SQL Server log simple DML operations on it's own?

     

    You have to dig depper into it on your own and provide much more informations for us.

  • LainRobertson's avatar
    LainRobertson
    Silver Contributor
    Mar 31, 2023

    gnanny 

     

    Do you mean the .NET SqlBulkCopy class (referenced below), or some method from another class?

     

     

    If it's the SqlBulkCopy class that you're talking about then you're looking at the wrong end: it's not your destination table(s) at fault; it's the source table/query used to generate the data.

     

    The target table is akin to a dumb terminal that receives whatever is contained in the source table or query, so the problem is definitely not at that end - or perhaps I should clarify and say that it's the SqlBulkCopy.WriteToServer() is not the cause of the fault (since if your package contains additional code after the bulk copy's WriteToServer() then I suppose anything is possible.)

     

    Cheers,

    Lain

  • Ronen_Ariely's avatar
    Ronen_Ariely
    MVP
    Apr 02, 2023

    Hi gnanny 

     

    You probably familiar with the sentence "the total is more than the sum of its parts".

     

    You present two behaviors which we should think about together and not solve each separately.

     

    The combination of "no primary key => loads same records 5 times" and the fact that "when primary key => failure with primary key constraint" clearly is a result of attempting to INSERT the data multiple times.  The issue is with your application code as it seems.

     

    using Bulk Insert in the correct way does not INSERT the same row more than once - not if it has PK or not.

    Seems like your app run the insert 5 times so if the there is no PK then it INERT the 5 tims and if there is PK then it cannot insert the second time.

     

    To help more than this we will need to see the entire code of the application that relevant to this. In anyway, this means that the issue is not in the SQL Server side and should be in a different forum under .Net or C#.