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

Question

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.

how to get identity in the sense can you explain clearly what is the requirement — Chanukya, Apr 09 '19 at 09:45
@Chanukya I need to all deleted record data from `SourceTable` and new ID from `ArchiveTable` that it assigned to each record after insert into `ArchiveTable` — Fred, Apr 09 '19 at 09:50
as per my understanding ArchiveTable how newid is inserting into ArchiveTable — Chanukya, Apr 09 '19 at 09:53
@Chanukya `ArchiveTable.ID` is a IDENTITY column and it has identity increment rule — Fred, Apr 09 '19 at 10:00

Sergey Menshov · Answer 1 · 2019-04-10T07:43:46.017

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

I would use a table variable for this, but the idea is the right idea. — Gordon Linoff, Apr 09 '19 at 10:58
@Gordon Linoff - Thank you for your comment! I've added a variant with a table variable too. It's my habit to use temporary tables. I've worked with MSSQL from 2000 version. — Sergey Menshov, Apr 10 '19 at 07:49

score 0 · Answer 2 · answered Apr 30 '19 at 03:58

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.

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

2 Answers2

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