Here i give simple example of delete statement with output clause.
if you want track the deleted row information at that time generally
we preffered trigger right?
here we inserte the deleted row in the other history table. we create
the two talbe one is data table and other for history purpose.
1) Create Table (Name:Table_1)
GO
CREATE TABLE [dbo].[Table_1]
( [id] [int] IDENTITY(1,1) NOT NULL,
[varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
2) Create Table For Save Deleted Record in other table (DeletedTblInfo)
GO
CREATE TABLE [dbo].[DeletedTblInfo]
( [DeletedTableInfoId] [int] IDENTITY(1,1) NOT NULL,
[DeletedTableId] [int] NULL,
[DeletedRowId] [int] NULL,
[DeletedDateTime] [datetime] NULL,
CONSTRAINT [PK_DeletedTblInfo] PRIMARY KEY CLUSTERED ( [DeletedTableInfoId] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
3) insert data into data table
go
INSERT INTO Table_1
(code)
VALUES
(‘test1′)
go
INSERT INTO Table_1
(code)
VALUES
(‘test2′)
go
INSERT INTO Table_1
(code)
VALUES
(‘test3′)
go
INSERT INTO Table_1
(code)
VALUES
(‘test4′)
4) now delete from table 1 where id =4
–here the record save in the DeletedTblInfo
–DeletedTableId : the table id where data is deleted or the table name if you want then.
–DeletedRowId : deleted RowId
–DeletedDateTime : deleted DateTime
DELETE FROM Table_1
OUTPUT 1,DELETED.ID,getdate()
INTO DeletedTblInfo WHERE (Id = 4);
–now see the result in the history table
DeletedTableInfoId DeletedTableId DeletedRowId DeletedDateTime 1 1 4 5/7/2008 12:53:52 PM
