sqlbulkcopy insert into sql server

本文关键字:server sql into insert sqlbulkcopy | 更新日期: 2023-09-27 18:03:09

在我的sqlserver表中我定义了以下列:stationid、dateofevent itemname sitename,点击

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.

的问候Sangeetha

sqlbulkcopy insert into sql server

绝对有一种方法可以在SqlBulkCopy中做检查-通过不做它们。

不要插入到最终表中(无论如何,SqlBulkCopy在其锁定上有严重的错误编程),而是插入到临时表中(您可以动态创建)。

然后可以执行自定义SQL将数据移动到最终表中,例如使用MERGE语句避免重复。这不仅会给你更好的多客户端行为(因为你避免了在rel数据表上的SqlBulkCopy的真正糟糕的锁行为),而且还允许各种ETL的东西发生与上传的数据。