C # SqlBulkCopy Avoid inserting duplicate data (insert without duplication)

Previously wrote a C # SqlBulkCopy a large amount of data into a database describes the efficient way to import large amounts of data into a database.

This article is related to this, before the thought is to do a full-scale insertion, each run the job will be emptied and then inserted, but in the face of large amounts of data, each time the flow is very large, especially when the data from a number of paid APIs, undoubtedly increased a lot of overhead. So we only get the updated data within 7 days and then select the non-inserted data to insert into the table, incremental operation of the table, thus achieving the purpose of controlling costs.

Before reading a lot of blog has introduced the solution to this problem, the use of some of the knowledge of the table and trigger, I am very much understand, the project is relatively tight, did not study, then give up, introduce a relatively simple method.

Introduce other people's formal solution for example, sqlbulkcopy with triggers, BULK INSERT table (existing is updated, not present then insert), interested can study under

/////////////////////////////////////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////

Idea:

We will get to the data that need to be inserted into a temporary table A_tem (this temporary table is the one I create every time I execute, the end of the deleted table)

Table A table A_tem

　　　　

Create a stored procedure

Select *  from A_tem  exceptselect* from A

This results in the data that is not present in table A that needs to be inserted

Result

-------------------------------------------------------------------------------------------

Code Sample:

String connstring=Configurationmanager.connectionstrings["ConnString"].                    ToString (); SqlConnection Conn=new SqlConnection (connstring); Conn.Open(); Logger.logmessage (String. Format ("CreateingTemporary Tablecsvfilevcqdata_tem ... "); //CreaterTemporary TableString sql="CREATE TABLECsvfilevcqdata_tem ([stringformatted] [nvarchar](Max)NULL,[ProjectName] [nvarchar]( the)NULL,[Resid_md4hash] [nvarchar](Max)NULL,[Wordcount] [Numeric]( -,0)NULL,[Charactercount] [Numeric]( -,0)NULL,[Sentancecount] [Numeric]( -,0)NULL,[Singleword] [bit] NULL,[twowordsonly] [bit] NULL,[haspunctuation] [bit] NULL,[Containsproductname] [bit]NULL,[Hasplaceholder] [bit] NULL,[Endsincolon] [bit]  NULL,[Hasvcq] [bit] NULL,[hasdevcomment] [bit] NULL,[hasloccomment] [bit] NULL,[Hasscreenshot] [bit] NULL,[IsMobile] [bit] NULL,[Isdesktop] [bit] NULL,[IsWeb] [bit] NULL,[Isconsumer] [bit] NULL,[isbusiness] [bit] NULL,[Totalsteelheadresults] [nvarchar]( the)NULL,[steelheadpasses] [Numeric]( -,0)NULL,[Steelheadfailures] [Numeric]( -,0)NULL,[S_pass_over_total] [nvarchar]( the)NULL,[S_failed_over_total] [nvarchar]( the)NULL,[Scoredlabels] [bit] NULL,[scoredprobabilities] [nvarchar]( the)NULL) on[PRIMARY]textimage_on[PRIMARY]";                    Execute_sql (SQL); SqlBulkCopy SqlBulkCopy=New SqlBulkCopy (conn); SqlBulkCopy. DestinationTableName="Csvfilevcqdata_tem"; //SqlBulkCopy. DestinationTableName=Itemtable.tablename;//the table name in the database is SqlBulkCopy. Bulkcopytimeout=  -; Logger.logmessage (String. Format ("Finding"+Itemtable.rows.Count +"Pieces ofdata ")); Logger.logmessage (String. Format ("Importing intoTheTemporary Table......")); DataTable datnew=ItemTable.DefaultView.ToTable (False, new string[]{"stringformatted", "ProjectName", "Resid_md4hash", "Wordcount", "Charactercount", "Sentancecount", "Singleword", "twowordsonly", "Haspunctuation", "Containsproductname", "HASPL Aceholder "," Endsincolon "," Hasvcq "," Hasdevcomment "," Hasloccomment "," Hasscreenshot "," IsMobile "," IsDe Sktop "," IsWeb "," Isconsumer "," isbusiness "," Total Steelhead Results "," steelheadpasses "," Steelheadfai                    Lures "," S_pass_over_total "," S_failed_over_total "," scored Labels "," scored probabilities "                    }); SqlBulkCopy.                    WriteToServer (datnew); DBHelper DBH=new DBHelper (); DataTable result=DBH. Executedatatable ("Get_newdata");//call the stored procedure Logger.logmessage (string. Format ("Finding"+Result. Rows.Count+"Pieces ofnew Data ... ")); SqlBulkCopy. DestinationTableName="Csvfilevcqdata"; SqlBulkCopy.                    WriteToServer (result); Logger.logmessage (String. Format ("DropingTemporary Tablecsvfilevcqdata_tem ... "); String Sql_="Drop TableCsvfilevcqdata_tem ";                    Execute_sql (SQL_); Conn.Close(); Logger.logmessage (String. Format ("Operation Done!"));

C # SqlBulkCopy Avoid inserting duplicate data (insert without duplication)