USE MYDB
GO
CREATE TABLE Employee
(
EMP_Id INT IDENTITY(1,1) NOT NULL,
Emp_Name VARCHAR(100) NOT NULL,
Emp_LastName VARCHAR(100) ,
Emp_DOB DATE,
emp_DOJ DATETIME DEFAULT GETDATE()
)GOINSERT INTO Employee(Emp_Name,Emp_LastName,Emp_DOB) OUTPUT inserted.*VALUES ('William','George','1986-04-12')
GOUPDATE Employee SET Emp_LastName='John' OUTPUT deleted.*,inserted.*WHERE Emp_id=1GODELETE FROM Employee OUTPUT deleted.* WHERE Emp_id=1
The output of the above statements shows capturing the values of identity column/ column which has default values is much easier using the OUTPUT clause. The output of the OUTPUT clause can be put it in a table or a table variable. Let us see a sample below:
--Inserting the output
of output clause into TableVariableDECLARE
@Employee TABLE (Emp_id INT,Emp_name VARCHAR(100),Emp_DOJ DATETIME)
INSERT INTO Employee(Emp_Name,Emp_LastName,Emp_DOF)OUTPUT inserted.emp_id,inserted.emp_name,inserted.emp_DOJ
INTO @Employee VALUES
('William','George','1986-04-12')
SELECT * FROM @Employee GO
--Inserting the
output of output clause into Table for maintainging the historyCREATE TABLE Employee_History
(
History_id INT
IDENTITY(1,1) NOT NULL PRIMARY
KEY,
EMP_Id INT NOT
NULL ,
Emp_Name VARCHAR(100) NOT
NULL,
Emp_LastName VARCHAR(100) ,
Emp_DOB
DATE ,
emp_DOJ DATETIME ,
InsertdDate DATETIME )GO
UPDATE Employee
SET Emp_LastName='John' OUTPUT
deleted.*,GETDATE() INTO Employee_History(emp_id,Emp_Name,Emp_LastName,Emp_DOB,Emp_DOJ,InsertdDate)WHERE Emp_id=1
