CODEWITHSUNDEEP
sqlsql-servert-sqloutput-clause
Fred
Fred

Reputation: 3421

Get Identity of destination table when using **DELETE FROM ... OUTPUT ... INTO**

I use bellow code to archive old data in ArchiveTable and delete archived data from SourceTable 

DELETE FROM SourceTable 
OUTPUT 
    DELETED.[ID], 
    DELETED.[Code],
    DELETED.[Title]
INTO ArchiveTable([OldID], [Code], [Title])
WHERE Condition

Structure of tables:

CREATE TABLE [SourceTable](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [Code] [VARCHAR](16) NULL,
    [Title] [NVARCHAR](128) NULL,
    CONSTRAINT [PK_SourceTable] PRIMARY KEY CLUSTERED ([ID] ASC)
) 
GO

CREATE TABLE [ArchiveTable](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [OldID] [INT] NOT NULL,
    [Code] [VARCHAR](16) NULL,
    [Title] [NVARCHAR](128) NULL,
    CONSTRAINT [PK_ArchiveTable] PRIMARY KEY CLUSTERED ([ID] ASC)
) 
GO

I need to return deleted records and ArchiveTable.[ID] to application. I change the code like this:

DELETE FROM SourceTable 
OUTPUT 
    DELETED.[ID], 
    DELETED.[Code],
    DELETED.[Title]
INTO ArchiveTable([OldID], [Code], [Title])
OUTPUT DELETED.*
WHERE Condition

This code return deleted records but I don't know how to get ID of ArchiveTable for this records. Look at ArchiveTable structure, It has OldID column that refer to SourceTable.ID and ID column that it is an Identity column of ArchiveTable. I need to ArchiveTable.ID in final result.

Upvotes: 2

Views: 353

Answers (3)

Luke
Luke

Reputation: 1

I encountered the same issue and this resolved it. First make sure you set this on the target table;

SET IDENTITY_INSERT [schema].[target_table_name] ON

And then define all the columns in both the DELETED section and the INTO list;

 DELETE [schema].[source_table]
 OUTPUT DELETED.[col1],
        DELETED.[col2],
        DELETED.[col3] 
    INTO [dest_table] ([col1],
                       [col2],
                       [col3])  
        FROM [schema].[source_table]

Upvotes: -1

Sergey Menshov
Sergey Menshov

Reputation: 3906

I found an interesting variant using DML with OUTPUT in SP and INSERT...EXEC... after that:

Test tables:

CREATE TABLE TestTable(
  ID int NOT NULL PRIMARY KEY,
  Title varchar(10) NOT NULL
)

CREATE TABLE TestTableLog(
  LogID int NOT NULL IDENTITY,
  OperType char(1) NOT NULL,
  CHECK(OperType IN('I','U','D')),
  ID int NOT NULL,
  Title varchar(10) NOT NULL
)

DML procedures:

CREATE PROC InsTestTable
  @ID int,
  @Title varchar(10)
AS

  INSERT TestTable(ID,Title)
  OUTPUT inserted.ID,inserted.Title,'I' OperType
  VALUES(@ID,@Title)

GO

CREATE PROC UpdTestTable
  @ID int,
  @Title varchar(10)
AS

  UPDATE TestTable
  SET
    Title=@Title
  OUTPUT inserted.ID,inserted.Title,'U' OperType
  WHERE ID=@ID

GO

CREATE PROC DelTestTable
  @ID int
AS

  DELETE TestTable
  OUTPUT deleted.ID,deleted.Title,'D' OperType
  WHERE ID=@ID

GO

Tests:

-- insert test
INSERT TestTableLog(ID,Title,OperType)
EXEC InsTestTable 1,'A'

INSERT TestTableLog(ID,Title,OperType)
EXEC InsTestTable 2,'B'

INSERT TestTableLog(ID,Title,OperType)
EXEC InsTestTable 3,'C'

-- update test
INSERT TestTableLog(ID,Title,OperType)
EXEC UpdTestTable 2,'BBB'

-- delete test
INSERT TestTableLog(ID,Title,OperType)
EXEC DelTestTable 3
GO

-- show resutls
SELECT *
FROM TestTableLog

Maybe it'll be interesting to someone.

Upvotes: 0

Sergey Menshov
Sergey Menshov

Reputation: 3906

You can use a temporary table

CREATE TABLE #DeletedRows(
    [ID] [INT] NOT NULL,
    [Code] [VARCHAR](16) NULL,
    [Title] [NVARCHAR](128) NULL
)

DELETE SourceTable 
OUTPUT 
    DELETED.[ID], 
    DELETED.[Code],
    DELETED.[Title]
INTO #DeletedRows([ID], [Code], [Title])
WHERE Condition

INSERT ArchiveTable([OldID], [Code], [Title])
OUTPUT INSERTED.*
SELECT [ID], [Code], [Title]
FROM #DeletedRows

DROP TABLE #DeletedRows

A variant with a table variable

DECLARE @DeletedRows TABLE(
    [ID] [INT] NOT NULL,
    [Code] [VARCHAR](16) NULL,
    [Title] [NVARCHAR](128) NULL
)

DELETE SourceTable 
OUTPUT 
    DELETED.[ID], 
    DELETED.[Code],
    DELETED.[Title]
INTO @DeletedRows([ID], [Code], [Title])
WHERE Condition

INSERT ArchiveTable([OldID], [Code], [Title])
OUTPUT INSERTED.*
SELECT [ID], [Code], [Title]
FROM @DeletedRows

Upvotes: 3

Related Questions