Magic tables in SQL Server.

Hello Guys,

For those of you who wants to save the history where ever there is Insertion, Updation or Deletion in a table there is a very easy way you can do this in SQL server.

This can mainly come across when you are using some transaction process or some accounting system where you have to keep log of every records and cant miss any of them, or I may say now a days you maintain history of each and every process.

So the query goes something like this

/* Delete Query */

DELETE MS

OUTPUT DELETED.Col1,DELETED.Col2,....,DELETED.Coln

INTO Table_Name_History

Col1,Col2,....,Col2

FROM Table_Name MS

WHERE col1 = 'abc' -- This is optional if you want a where clause or not.

There is a fact about which many of us are not aware, When there is any update statement fired on a table or table's row at that time SQL server internally deletes the row and creates a new row with the updated value. Deleted row goes into DELETED Magic table and Inserted row goes into INSERTED Magic Table

/*Update Query to maintain History*/

Update MS

Set MS.col1 = 2,MS.col2=3....,MS.Coln = 3

OUTPUT DELETED.Col1,DELETED.Col2,....,DELETED.Coln

INTO Table_Name_History

From Table_Name MS 
 

/*Update Query to maintain History*/

Update MS

Set MS.col1 = 2,MS.col2=3....,MS.Coln = 3

OUTPUT INSERTED.Col1,INSERTED.Col2,....,INSERTED.Coln

INTO Table_Name_History

From Table_Name MS

This magic table's will not be accessible explicitly like,
You open New Query window in your SQL server management studio(SSMS) and you fire either of the following query,
SELECT * FROM INSERTED,
SELECT * FROM DELETED


Its scope is until you fire any Insert,Update,Delete Statement or if you are using them in Stored Procedure and till the scope of that SP expires.

For Better understanding review this link

http://msdn.microsoft.com/en-us/library/ms177564.aspx