Delete Original Data and maintain history with single SQl statement......(Magic tables)
-In SQL Server there are two system tables “Inserted” and “Deleted” called Magic tables.
-These are not the physical tables but the virtual tables generally used with the triggers to retrieve the inserted, deleted or updated rows.
-When a record is inserted in the table that record will be there on INSERTED Magic table.
-When a record is updated in the table that existing record will be there on DELETED Magic table and modified data will be there in INSERTED Magic table.
-When a record is deleted from that table that record will be there on DELETED Magic table.
Hello friends, one interviewer asked that suppose you have two tables
Employee_Details, Employee_Details_History now I want to delete original table
Employee_details and maintain it’s history to other table.How can it’s possible with only single SQL statement?
drop table Employee_Details,Employee_Details_History
CREATE TABLE Employee_Details(UserId int,UserName varchar(100),FirstName varchar(100),LastName varchar(100),City varchar(100),Designation varchar(100))
CREATE TABLE Employee_Details_History
(UserId int,UserName varchar(100),FirstName varchar(100),LastName varchar(100),City varchar(100),Designation varchar(100))
insert into Employee_Details values(1,'Suresh','Suresh','E','Vizag','Software Engineer'),
(2,'Mahesh','Mahesh','D','Hyd','Test Engineer')
select * from Employee_Details
delete from Employee_Details
OUTPUT deleted.* into Employee_Details_History
select * from Employee_Details
select * from Employee_Details_History
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment