sqlbulkcopy insert into sql server

Question

In my sqlserver table i have following columns defined, stationid,dateofevent,itemname,sitename,clicks

To populate above table , we have a c# application. In which inserts data in a loop. Data comes from remote machine and once data received by server(another c# application) , it imserts into sql server and send back OK response to remote client. When client receives response , it archives data into another table and deletes the data from actual table.

Incase if client fails to archive , stored procedure from server side will take care of preventing duplicate record insert.

    Set @previousClickCount=( SELECT Clicks FROM [Analytics] as pc
                                 where DATEADD(dd, 0, DATEDIFF(dd, 0, [DateOfEvent]))=@date
                                 and ItemType=@type
                                 and stationId = @stationId 
                                 and ItemName=@itemName 
                                 and SiteName=@siteName)
    If @previousClickCount Is Null
        Begin
        -- Row for this item is not found in DB so inserting a new row
            Insert into Analytics(StationId,DateOfEvent,WeekOfYear,MonthOfYear,Year,ItemType,ItemName,Clicks,SiteName)
            VALUES(@stationId,@date,DATEPART(wk,@date),DATEPART(mm,@date),DATEPART(YYYY,@date),@type,@itemName,@clicks,@siteName)
        End

Later we decided to move to bulk insert in server side code. So that we can avoid looping.             

bulkCopy.DestinationTableName = SqlTableName;
bulkCopy.BatchSize = 1000;
bulkCopy.BulkCopyTimeout = 1000;
bulkCopy.WriteToServer(dataTable);

But incase client side failed to archive data then server will insert duplicate record.Is there any way to check this in bulk insert or whether can we add any constraint like,insert only if the itemname not present for the particular date then insert.

Regards Sangeetha

bulkcopy does just that - a straight copy. no validation, nothing of the sort. you can maybe add triggers on the tables so when the copy finishes or the batch finishes and gets committed, the triggers will fire but any exceptions will no doubt not commit the batch. — Ahmed ilyas, May 13 '14 at 09:29

score 0 · Accepted Answer · answered May 13 '14 at 09:36

There is absolutely a way to do checks in SqlBulkCopy - by not doing them.

DO not insert into the final table (which is better anyway, SqlBulkCopy has serious bad programming on it's locking) but into a temporary table (that you can create dynamically).

Then you can execute custom SQL to move the data into the final table, for example with a MERGE statement that avoids duplicates. THis not only will give you a lot better multi client behavior (as you avoid the really bad lock behavior of SqlBulkCopy on the rel dat table) but also allows all kinds of ETL stuff to happen with the uploaded data.

sqlbulkcopy insert into sql server

1 Answers1