SQL Server batch swipe data to perform a transaction rollback statement backup

The enterprise performs brush data work on the database, and a long statement is expected to succeed or fail at the same time.

1. Setting up a test environment

/************************************************************ * Code formatted by Softtree SQL Assistant? v6.5.278 * Time:2016/9/29 21:33:55 ************************************************************/---Establish test environmentCREATE DATABASEtesttransaction UsetesttransactionGOCREATE TABLEtesttable (TestIDINT PRIMARY KEY IDENTITY, TestNameVARCHAR( -) not NULL);

2. Execution of transactions

---StartBEGINTRYBEGIN TRANSACTION        INSERT  intoTestTableVALUES('3')              INSERT  intoTestTableVALUES('5')         INSERT  intoTestTableVALUES( (SELECTTestName fromTestTableWHERETestID=1))---Perform insert on record with ID 1         INSERT  intoTestTableVALUES( (SELECT MAX(testname) fromTestTableWHERETestID=1))---aggregate function ensures that only one entry is returned    COMMIT TRANSACTIONENDTRYBEGINCATCHROLLBACK TRANSACTION    PRINT 'There was a statement insert failure and the transaction was rolled back'ENDCatch

Test commit and rollback transactions are OK.

3. Auxiliary statements

-- -delete table, restore self-increment starting from 1 TRUNCATE TABLE testtable -- -Show insert self-increment column SET Identity_insert  on INSERT  into VALUES (+,'5') SET Identity_insert off

Attention:

1.set Identity_insert is only valid for the current session.

2.Set identity_insert table name on set, you must display the specified ID (insert into the column into which you want to write the ID), or insert the error. If INSERT into table_name values (' 111 ') will be an error.

Inserts the specified value to the self-increment ID. Error: " cannot insert an explicit value for the Identity column in table" When IDENTITY_INSERT is set to OFF .

The INSERT statement does not display the specified ID. Error: "You can specify an explicit value for the identity column in the table only if the column list is used and Identity_insert is on."

SQL Server batch swipe data to perform a transaction rollback statement backup