I would like to discuss Magic Tables in SQL Server.
As per my studies I found that there are 2 Magic Tables; they are:
- Inserted
- 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:
- Insert
- Delete
- 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 |