How to Use Output to Capture New and Old Id

How to use OUTPUT to capture new and old ID?

You can't use traditional INSERT to output non-inserted fields.
Use merge instead (caveat: only works with database compatibility >= 100):

MERGE
  #tempproduct AS t
USING(
  SELECT
      Name
      , ID
  FROM
    product) AS s
ON (1=0)  
WHEN NOT MATCHED   
  THEN INSERT (Name) VALUES (Name)
OUTPUT inserted.ID, s.ID INTO @OutputAttributesValues;

SELECT * FROM @OutputAttributesValues

T-SQL Output Clause: How to access the old Identity ID

You'll have to join back @act_map_matrix onto Act to get the "old" value.

It's simply not available in the INSERT statement

edit: one hopes that @new_scriptID and "scriptid=2" could be the join column

How do I use an INSERT statement's OUTPUT clause to get the identity value?

You can either have the newly inserted ID being output to the SSMS console like this:

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

You can use this also from e.g. C#, when you need to get the ID back to your calling app - just execute the SQL query with .ExecuteScalar() (instead of .ExecuteNonQuery()) to read the resulting ID back.

Or if you need to capture the newly inserted ID inside T-SQL (e.g. for later further processing), you need to create a table variable:

DECLARE @OutputTbl TABLE (ID INT)

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID INTO @OutputTbl(ID)
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

This way, you can put multiple values into @OutputTbl and do further processing on those. You could also use a "regular" temporary table (#temp) or even a "real" persistent table as your "output target" here.

Retrieve original and new identities mapping from SELECT INSERT statement using OUTPUT clause

It can be achieved using MERGE INTO and OUTPUT:

MERGE INTO MyTable AS tgt
USING MyTable AS src ON 1=0 --Never match
WHEN NOT MATCHED THEN
INSERT (Name)
VALUES (src.Name)
OUTPUT
    src.Id,
    inserted.Id
INTO @idsMap;

Using output to capture auto increment identity, plus past identity mapping table

I ended up using MERGE INTO to keep track of the old ID as per the following SO post:
How to use OUTPUT to capture new and old ID?

Insert into with output clause

If table 1 and table 2 have a 1:1 relationship, and no foreign key exists between the two then you could do this in a single statement:

MERGE Table1 AS a 
USING
(   SELECT  A.[group], A.account, B.title, B.amount, B.id2    
    FROM    Table1 AS A
            LEFT OUTER JOIN Table2 AS B
                ON A.id = B.id2
) AS b
    ON 1 = 0
WHEN NOT MATCHED THEN 
    INSERT ([group], account)
    VALUES (b.[group], b.account)
OUTPUT inserted.Id, B.title, B.amount 
    INTO Table2(id2, title, amount);

Example on SQL Fiddle

Realistically though, if your tables are related they should have a foreign key, and in most cases they won't be 1:1, rather 1:n.

In which case you would still need to use MERGE to caputre both the new ID and the old ID, but you would then need to capture this mapping in a temporary table before performing a second insert to Table2:

DECLARE @Map TABLE (OldID INT NOT NULL, NewID INT NOT NULL);

MERGE Table1 AS a 
USING
(   SELECT  A.ID, A.[group], A.account  
    FROM    Table1 AS A
) AS b
    ON 1 = 0
WHEN NOT MATCHED THEN 
    INSERT ([group], account)
    VALUES (b.[group], b.account)
OUTPUT inserted.Id, b.ID
    INTO @Map(NewID, OldID);

INSERT Table2 (id2, title, amount)
SELECT  m.NewID, b.title, b.amount
FROM    @Map AS m
        INNER JOIN Table2 AS b
            ON b.ID2 = m.OldID;

Example on SQL Fiddle

Using merge..output to get mapping between source.id and target.id

In my opinion this is a great use of MERGE and output. I've used in several scenarios and haven't experienced any oddities to date.
For example, here is test setup that clones a Folder and all Files (identity) within it into a newly created Folder (guid).

DECLARE @FolderIndex TABLE (FolderId UNIQUEIDENTIFIER PRIMARY KEY, FolderName varchar(25));
INSERT INTO @FolderIndex 
    (FolderId, FolderName)
    VALUES(newid(), 'OriginalFolder');

DECLARE @FileIndex TABLE (FileId int identity(1,1) PRIMARY KEY, FileName varchar(10));
INSERT INTO @FileIndex 
    (FileName)
    VALUES('test.txt');

DECLARE @FileFolder TABLE (FolderId UNIQUEIDENTIFIER, FileId int, PRIMARY KEY(FolderId, FileId));
INSERT INTO @FileFolder 
    (FolderId, FileId)
    SELECT  FolderId, 
            FileId
    FROM    @FolderIndex
    CROSS JOIN  @FileIndex;  -- just to illustrate

DECLARE @sFolder TABLE (FromFolderId UNIQUEIDENTIFIER, ToFolderId UNIQUEIDENTIFIER);
DECLARE @sFile TABLE (FromFileId int, ToFileId int);

-- copy Folder Structure
MERGE @FolderIndex fi
USING   (   SELECT  1 [Dummy],
                    FolderId, 
                    FolderName
            FROM    @FolderIndex [fi]
            WHERE   FolderName = 'OriginalFolder'
        ) d ON  d.Dummy = 0
WHEN NOT MATCHED 
THEN INSERT 
    (FolderId, FolderName)
    VALUES (newid(), 'copy_'+FolderName)
OUTPUT  d.FolderId,
        INSERTED.FolderId
INTO    @sFolder (FromFolderId, toFolderId);

-- copy File structure
MERGE   @FileIndex fi
USING   (   SELECT  1 [Dummy],
                    fi.FileId, 
                    fi.[FileName]
            FROM    @FileIndex fi
            INNER
            JOIN    @FileFolder fm ON 
                    fi.FileId = fm.FileId
            INNER
            JOIN    @FolderIndex fo ON 
                    fm.FolderId = fo.FolderId
            WHERE   fo.FolderName = 'OriginalFolder'
        ) d ON  d.Dummy = 0
WHEN NOT MATCHED 
THEN INSERT ([FileName])
    VALUES ([FileName])
OUTPUT  d.FileId,
        INSERTED.FileId
INTO    @sFile (FromFileId, toFileId);

-- link new files to Folders
INSERT INTO @FileFolder (FileId, FolderId)
    SELECT  sfi.toFileId, sfo.toFolderId
    FROM    @FileFolder fm
    INNER
    JOIN    @sFile sfi ON  
            fm.FileId = sfi.FromFileId
    INNER
    JOIN    @sFolder sfo ON 
            fm.FolderId = sfo.FromFolderId
-- return    
SELECT  * 
FROM    @FileIndex fi 
JOIN    @FileFolder ff ON  
        fi.FileId = ff.FileId 
JOIN    @FolderIndex fo ON  
        ff.FolderId = fo.FolderId

Combine OUTPUT inserted.id with value from selected row

You can (ab)use MERGE with OUTPUT clause.

MERGE can INSERT, UPDATE and DELETE rows. In our case we need only to INSERT.
1=0 is always false, so the NOT MATCHED BY TARGET part is always executed.
In general, there could be other branches, see docs.
WHEN MATCHED is usually used to UPDATE;
WHEN NOT MATCHED BY SOURCE is usually used to DELETE, but we don't need them here.

This convoluted form of MERGE is equivalent to simple INSERT,
but unlike simple INSERT its OUTPUT clause allows to refer to the columns that we need.
It allows to retrieve columns from both source and destination tables thus saving a mapping between old and new IDs.

MERGE INTO [dbo].[Test]
USING
(
    SELECT [Data]
    FROM @Old AS O
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Data])
VALUES (Src.[Data])
OUTPUT Src.ID AS OldID, inserted.ID AS NewID
INTO @New(ID, [OtherID])
;

---

Regarding your update and relying on the order of generated IDENTITY values.

In the simple case, when [dbo].[Test] has IDENTITY column, then INSERT with ORDER BY will guarantee that the generated IDENTITY values would be in the specified order. See point 4 in Ordering guarantees in SQL Server. Mind you, it doesn't guarantee the physical order of inserted rows, but it guarantees the order in which IDENTITY values are generated.

INSERT INTO [dbo].[Test] ([Data])
SELECT [Data]
FROM @Old
ORDER BY [RowID]

But, when you use the OUTPUT clause:

INSERT INTO [dbo].[Test] ([Data])
OUTPUT inserted.[ID] INTO @New
SELECT [Data]
FROM @Old
ORDER BY [RowID]

the rows in the OUTPUT stream are not ordered. At least, strictly speaking, ORDER BY in the query applies to the primary INSERT operation, but there is nothing there that says what is the order of the OUTPUT. So, I would not try to rely on that. Either use MERGE or add an extra column to store the mapping between IDs explicitly.

How to copy rows with SQL and get new and old IDs as result?

With helpful links from Andriy M's link to 'How to copy tables avoiding cursors in SQL?', I managed to come up with this very elegant solution:

DECLARE @t TABLE (oID int, nID int);

MERGE T s
USING (
        SELECT TID, name, address
        FROM T [s]
      ) d on 0 = 1
WHEN NOT MATCHED
THEN INSERT (name, address)
    VALUES (name, address)
OUTPUT d.TID as oID, Inserted.TID as nID
INTO @t;

---

Related Topics