SQL Server database uses triggers and sqlbulkcopy to insert and update large batches of data, and triggers sqlbulkcopy

SQL Server database uses triggers and sqlbulkcopy to insert and update large batches of data, and triggers sqlbulkcopy
Knowledge needed

1. Trigger

2. sqlbulkcopy

My purpose

Tools for developing Database Synchronization require mass data insertion and data update.

Method

Use the SqlBulkCopy class to replicate data in batches. Create a temporary table with the same structure as the table to be synchronized. Then, write a trigger in the temporary table to insert and update the updated data table;

Trigger points

1. trigger is a special stored procedure. It is triggered when you Insert, Delete, or Update a table;

2. When a trigger is triggered, the system automatically creates the inserted and deleted tables. After the trigger is executed, the table is automatically deleted:

Inserted stores, adds, and updates data;

The deleted table stores the deleted and updated data;

3. AFTER and instead of are two types OF triggers:

AFTER is triggered AFTER an Insert, Delete, or Update operation is completed;The instead of trigger action must be processed earlier than the Table constraint;

Trigger content reference http://www.cnblogs.com/selene/p/4493311.html

SqlBulkCopy class

Sample Code

1 /// <summary> 2 /// SqlBulkCopy 3 /// </summary> 4 /// <param name = "connectionString"> Target connection character </param> 5 /// <param name = "TableName"> target table </param> 6 // <param name = "dt"> data source </param> 7 private void SqlBulkCopyByDatatable (string connectionString, string TableName, DataTable dt) 8 {9 using (SqlConnection conn = new SqlConnection (connectionString) 10 {11 using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy (connectionString, SqlBulkCopyOptions. fireTriggers) 12 {13 try14 {15 sqlbulkcopy. destinationTableName = TableName; 16 for (int I = 0; I <dt. columns. count; I ++) 17 {18 sqlbulkcopy. columnMappings. add (dt. columns [I]. columnName, dt. columns [I]. columnName); 19} 20 sqlbulkcopy. writeToServer (dt); 21} 22 catch (System. exception ex) 23 {24 throw ex; 25} 26} 27} 28}

MSDN Introduction

Specifically

Set the SqlBulkCopyOptions attribute to FireTriggers to activate the corresponding table trigger;

Instance

Create three tables with simple structure;

SELECT  Table_1.id ,        Table_1.nameFROM    dbo.Table_1;SELECT  Table_2.id ,        Table_2.nameFROM    dbo.Table_2;SELECT  Table_3.id ,        Table_3.nameFROM    dbo.Table_3;

Create a table

CREATE TABLE [dbo].[Table_1]    (      [id] [NCHAR](10) NULL ,      [name] [NCHAR](10) NULL    )ON  [PRIMARY];

When inserting data into Table_3,

Determine whether the same id exists in Table_1. If yes, update the name field. If no new data exists in Table_1.

Table Table_2 is mainly used to test the statements in the trigger;

Table_3 trigger

Create trigger [dbo]. [tri_Insert] ON [dbo]. [Table_3] instead of insert as -- UPDATE dbo exists. table_1 SET id = INSERTED. id, name = INSERTED. NAMEFROM Table_1 JOIN inserted ON Table_1.id = INSERTED. id -- INSERT dbo is not added. table_1 (id, name) select inserted. id, INSERTED. name from inserted left outer join Table_1 on inserted. id = Table_1.idWHERE Table_1.ID IS NULL

This trigger is used to determine whether Table_1 has this data when inserting data into Table_3. If this data exists, it is updated. If it does not exist, it is added;

For the newly added update statements, you can test the reason for writing in Table_2. The principle is very simple;

 

FQA

SqlBulkCopy:

The inserted DataTable must have the same structure as the target table. My approach is to first SELECT the table structure obtained FROM TableName WHERE 1 = 2;

Whether the SqlBulkCopy primary key is automatically updated and the same primary key is updated:

After testing, the primary key ID column is automatically auto-incrementing after being null in Insert, and the same primary key is not automatically updated, but is added;

At the same time, an exception is returned when the same ID is inserted when the newly added identity constraint is met. If the same ID value is assigned in time, the new identity cannot be updated;

AFTER and instead of different triggers

When you update data in batches to a trigger, instead of directly submits the data to the trigger for execution (Actions must be processed before the table's constraints ??), If the trigger statement does not have a new statement on the table, no data is added ;(?? Are there any deviations ??)

However, AFTER triggers are executed only AFTER the table operation is complete;

The purpose OF using instead of for bulk insert and update operations is to remove the need to clear temporary tables.