- A transaction is a single unit of work.
- If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database.
- If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
SQL Server operates in the following transaction modes:
- Autocommit transactions: Each individual statement is a transaction.
- Explicit transactions: Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
- Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
- By Default SQL Server uses AutoCommit Transactions i.e. after executing each statement it will automatically Commit it.
- If we want to use the Explicit Transactions before executing the statements we need to start with a Begin Transaction statement and then decide whether it has to be commited or rolledback, until the transaction ends the records gets locked.
- If we want to use the Implicit Transactions we should use the following Statement:
SET IMPLICIT_TRANSACTIONS ON | OFF
- When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode.
- To Manage the Transactions we have the TCL (Transaction Control Language) with 3 commands in it Commit, Rollback and Save Transaction.
Marks the end of a successful implicit or explicit transaction. COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction.
DELETE FROM EMP WHERE EMPNO=1015
Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction.
DELETE FROM EMP WHERE EMPNO=1014
A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. If a transaction is rolled back to a savepoint, it must proceed to completion with more Transact-SQL statements if needed and a COMMIT TRANSACTION statement, or it must be canceled altogether by rolling the transaction back to its beginning. To cancel an entire transaction, use the ROLLBACK TRANSACTION statements.
UPDATE EMP SET SAL=5000 WHERE EMPNO=1001
SAVE TRANSACTION S1
UPDATE EMP SET SAL=5000 WHERE EMPNO=1002
SAVE TRANSACTION S2
UPDATE EMP SET SAL=5000 WHERE EMPNO=1003
ROLLBACK TRANSACTION S2 OR Rollback ROLLBACK TRANSACTION S1
-In the above case either the last statement gets rolled back or the last 2 statements gets rolled back and commits the rest.
Creating a table from an existing table:
We can create a table from an existing table maintain a copy of the actual table before manipulating the data.
Syntax: SELECT < * | <COLLIST > INTO <NEW TNAME> FROM <OLD TNAME> [CONDITIONS]
SELECT * INTO NEW_EMP FROM EMP
-In this case it creates a table NEW_EMP by copying all the rows and columns of the EMP table.
SELECT EMPNO, ENAME, SAL, DEPTNO INTO TEST_EMP FROM EMP
-In this case it creates a table TEST_EMP with only the specified columns from the EMP table.
SELECT * INTO SALES_EMP FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=’SALES’)
-In this case it creates a table SALES_EMP with only the information of sales department from the EMP table.
SELECT * INTO DUMMY_EMP FROM EMP WHERE 1=2
-In this case it creates the DUMMY_EMP table with out any data in it.
Copying data from one existing table to another table:
We can copy the data from one table into another table by using a combination of insert and select statement as following:
Syntax: INSERT INTO <TNAME> [ (COLLIST) ]
SELECT < * | <COLLIST> FROM <TNAME> [CONDITIONS]
INSERT INTO DUMMY_EMP SELECT * FROM EMP
-In this case all the rows of EMP table is copied into DUMMY_EMP table.
INSERT INTO DUMMY_EMP (EMPNO, ENAME, SAL, DEPTNO)
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=30
-In this case it copies only the selected columns into the DUMMY_EMP table from the EMP table.
Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )