September 8, 2020 09:36 by
PeterToday, I am going to show how to use magic tables in the SQL server without using a trigger.
In our daily working environments, we need to maintain history or track of master data in transaction tables for a clear understanding of how data changed day by day. We have different approaches to achieve this work like creating triggers, temporal tables, and some other things. But, we achieve without creating triggers and temporal tables in our database. As all we know, there are two magic tables, i.e, inserted, and deleted. We use these in our triggers. In many scenarios, triggers are not preferable because they hold and block a full table when they fired. Today, I came up with a good approach of using those beautiful magic tables in our SQL server database with creating a trigger. For example, I want to insert all values into table2 from table1 after inserting the values in table1 by using a single insert statement.
First, I am creating two tables in my database with the following code:
CREATE TABLE Table1 (ID1 INT, Col1 VARCHAR(100))
GO
CREATE TABLE Table2 (ID2 INT, Col2 VARCHAR(100))
GO
After that, I am inserting values in table1 by using the insert statement and inserting it into table2 at a time without using another insert statement, but I am using a magic table with the below code:
INSERT INTO Table1 (ID1, Col1)
OUTPUT inserted.ID1, inserted.Col1
INTO Table2
VALUES(1,'Col'), (2, 'Col2')
GO
Check the result after executing the above code whether the data inserted into a table2 or not using the select statements.
SELECT *
FROM Table1
GO
SELECT *
FROM Table2
GO
I got the output as follows:
In the same way, we can insert fetched records from another table by using a select statement. in General, we use to insert data into a table that is fetched by using the select command. You can observe the code below for a better understanding.
insert into #temp1(BankAccountTransactionId)
output inserted.BankAccountTransactionId into #temp2
select BankAccountTransactionId
from Bankaccounttransactions (nolock) where transactiondate ='2020-08-01'
go
In the above query, I inserted data into #temp1 that was fetched from my table present in my database. You can observe the output by selecting records from both #temp1 and #temp2 tables.
In the same way, we can use our other magic table deleted to track old data. Here I am updating data in col1 column agonist id1 = 1 in table1 and you observe in table2, a record was inserted with the old value of table one. For a clear understanding, truncate data in table2 before going to update the data in table1. Execute the below code.
update Table1 set col1 = 'col3'
OUTPUT deleted.ID1, deleted.Col1
INTO Table2
where ID1 = 1
I am updating the record in col1 column against id1=1. For a better understanding of data changes in the table, I want to track history in table2. I can store old data in table2 by using the above code. The output is as follows:
We can also store them in log tables which we were deleted by using a magic table deleted. Generally, we never use such scenarios as the track of deleted records. I want to delete records from #temp1 but I need to store the records in my audit table I;e, #temp2. The below code will help in this scenario.
delete from Table1
OUTPUT deleted.ID1, deleted.Col1
INTO Table2
See the output below by executing the following command:
SELECT *
FROM Table1
GO
SELECT *
FROM Table2
GO
Notice that I can store old data and insert data into multiple tables at a time using a single insert statement without using triggers and temporal tables.