Magic Tables in SQL Server

I would like to discuss Magic Tables in SQL Server.

As per my studies I found that there are 2 Magic Tables; they are:

1. Inserted
2. Deleted

Before starting the description of Magic Tables I would like to discuss the following.

I have one question for anyone reading this article.

What happens when you update the table or when you run the Update command?

Is it going to really update the existing record?

No, it will first delete the record and then re-insert the new changed values or the reverse of that.

Magic Tables can be used with the following commands:

1. Insert
2. Delete
3. Update

Note: to use Magic Tables you have to use OUTPUT in the query.

I have a table named Student having the following fields:

iStudentID	vchStudentFirstName	vchStudentLastName	vchDepartment
1	FirstName1	LastName1	BCA
2	FirstName2	LastName2	MCA
3	FirstName3	LastName3	BCS
4	FirstName4	LastName4	BA

1. Insert

When we insert records into the table then the Inserted Table will be used and whatever we are inserting into the table is in the Inserted Table.

Query to demonstrate the Inserted Table used in an Insert Statement:

DROP TABLE #Student

CREATE TABLE #Student

(

      IStudentID int identity (1, 1)

      ,vchStudentFirstName varchar(100)

      , vchStudentLastName varchar (100)

      , vchDepartment varchar (50)

)

INSERT INTO #Student OUTPUT Inserted.* values ('FirstName1','LastName1','BCA')

2. Delete

When we delete records the Deleted Table will be used, and the deleted records are stored in the Deleted Table.

Query to demonstrate the Deleted Table used in a Delete Statement:

DROP TABLE #Student

CREATE TABLE #Student

(

      IStudentID int identity (1, 1)

      ,vchStudentFirstName varchar(100)

      , vchStudentLastName varchar (100)

      , vchDepartment varchar (50)

)

INSERT INTO #Student values ('FirstName1','LastName1','BCA')

INSERT INTO #Student values ('FirstName2','LastName2','MCA')

INSERT INTO #Student values ('FirstName3','LastName3','BCS')

INSERT INTO #Student values ('FirstName4','LastName4','BA') 

 

DELETE FROM #Student OUTPUT deleted.*  WHERE iStudentID=3

3. Update

When we update records then both the Inserted and Deleted Tables are used; first it will delete the record and then insert and store these results in the Inserted & Deleted Tables; see:
Query to demo Inserted & Deleted Table used in Update Statement,

DROP TABLE #Student

 

CREATE TABLE #Student

(

      iStudentID int identity(1,1)

      ,vchStudentFirstName varchar(100)

      ,vchStudentLastName varchar(100)

      ,vchDepartment varchar(50)

)

 

INSERT INTO #Student values ('FirstName1','LastName1','BCA')

INSERT INTO #Student values ('FirstName2','LastName2','MCA')

INSERT INTO #Student values ('FirstName3','LastName3','BCS')

INSERT INTO #Student values ('FirstName4','LastName4','BA')

 

UPDATE #STUDENT  SET vchDepartment='M-Com' output Inserted.*,Deleted.* WHERE iStudentID=4

Before Updating Department for Student having ID = 4:

iStudentID	vchStudentFirstName	vchStudentLastName	vchDepartment
1	FirstName1	LastName1	BCA
2	FirstName2	LastName2	MCA
3	FirstName3	LastName3	BCS
4	FirstName4	LastName4	BA

After Updating Department 'M-Com' for Student having ID = 4:

iStudentID	vchStudentFirstName	vchStudentLastName	vchDepartment
1	FirstName1	LastName1	BCA
2	FirstName2	LastName2	MCA
3	FirstName3	LastName3	BCS
4	FirstName4	LastName4	M-Com

UPDATE #STUDENT  SET vchDepartment='M-Com' output Inserted.*,Deleted.* WHERE iStudentID=4

Inserted Table

iStudentID	vchStudentFirstName	vchStudentLastName	vchDepartment
4	FirstName4	LastName4	M-Com

Deleted Table

iStudentID	vchStudentFirstName	vchStudentLastName	vchDepartment
4	FirstName4	LastName4	BA