ARTICLE

Magic Tables in SQL Server

Posted by Vikrant More Articles | SQL September 10, 2012
In this article I would like to discuss the Magic tables used in the SQL Server.
Reader Level:
 

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

COMMENT USING
Employers - Post Free Jobs