Use Output clause in Delete Statement in sqlserver 2005

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s