Combine Output Inserted.Id with Value from Selected Row

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.

Output Inserted.Id AND another field

You might want to explore MERGE:

MERGE INTO dbo.Products
USING dbo.BulkProducts AS src
    ON 1 = 0 -- Never match
WHEN NOT MATCHED THEN
    INSERT(EanCode, ChangedDateTime, ChangedById, Deleted)
    VALUES(src.EanCode, GETDATE(), GETDATE(), 0)
OUTPUT
    inserted.Id,
    src.Id
INTO @OutProduct;

Reference:

Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE by Adam Machanic

SQL Server : output inserted value as well as value from SELECT

As commented by Larnu, OUTPUT can not refer to columns coming from the SELECT part of the INSERT.

You could work around this with a MERGE statement:

MERGE INTO App.Table1
USING App.Table2 AS t2
ON 1 = 0
WHEN NOT MATCHED THEN INSERT((b, c)) Values(t2.b, t2.c)
OUTPUT inserted.a, t2.a INTO #TempTable

How to get inserted NewSequentialId value when doing multiple inserts?

The easiest (and likely most efficient) way would be to insert into the target MyTable table directly without the intermediate staging table. I'd use a table-valued-parameter to pass the table of values into your stored procedure.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

---

If you really want to use the staging table, you can't rely on the order of rows returned by the OUTPUT clause. You need to store the explicit mapping between the #MyTableStaging.RowOrder and the generated MyTable.Id. When you use OUTPUT clause in a simple INSERT statement you can't include columns from the source table into the output. There is a workaround. You can use MERGE instead of INSERT and OUTPUT clause of the MERGE statement allows columns from source table.

See very similar question Combine OUTPUT inserted.id with value from selected row

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.

DECLARE @MyTableOutput TABLE
(
    OldRowOrder int NOT NULL
    ,NewID UNIQUEIDENTIFIER NOT NULL
);

MERGE INTO dbo.MyTable
USING
(
    SELECT RowOrder, SomeNonUniqueValue
    FROM #MyTableStaging
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (SomeNonUniqueValue)
VALUES (Src.SomeNonUniqueValue)
OUTPUT Src.RowOrder AS OldRowOrder, inserted.ID AS NewID
INTO @MyTableOutput(OldRowOrder, NewID)
;

---

If your DBA is so afraid of MERGE you don't have to use it. It will be less efficient, though.

Simply insert all rows.

INSERT INTO dbo.MyTable (SomeNonUniqueValue)
SELECT SomeNonUniqueValue 
FROM #MyTableStaging
;

We don't care about the order.

If SomeNonUniqueValue were unique, you could just join on this column to map RowOrder to Id. Since these values are not unique, we'll need an extra step and generate unique row numbers for joining.

WITH
CTE_Dst
AS
(

    SELECT
        Id
        ,SomeNonUniqueValue
        ,ROW_NUMBER() OVER (ORDER BY SomeNonUniqueValue) AS rn
    FROM dbo.MyTable
)
,CTE_Src
AS
(

    SELECT
        RowOrder
        ,SomeNonUniqueValue
        ,ROW_NUMBER() OVER (ORDER BY SomeNonUniqueValue) AS rn
    FROM #MyTableStaging
)
SELECT
    CTE_Dst.Id
    ,CTE_Src.RowOrder
FROM
    CTE_Dst
    INNER JOIN CTE_Src ON CTE_Src.rn = CTE_Dst.rn
;

If you have, say, three rows with the same SomeNonUniqueValue it doesn't really matter how you map these rows together, because SomeNonUniqueValue is the same.

Example:

#MyTableStaging
+----------+--------------------+
| RowOrder | SomeNonUniqueValue |
+----------+--------------------+
|        1 | qwerty             |
|        2 | qwerty             |
|        3 | qwerty             |
|        4 | asdf               |
|        5 | asdf               |
+----------+--------------------+

MyTable
+----+--------------------+
| ID | SomeNonUniqueValue |
+----+--------------------+
| A  | qwerty             |
| B  | qwerty             |
| C  | qwerty             |
| D  | asdf               |
| E  | asdf               |
+----+--------------------+

You can map them like this:

+----------+----+--------------------+
| RowOrder | ID | SomeNonUniqueValue |
+----------+----+--------------------+
|        1 | A  | qwerty             |
|        2 | B  | qwerty             |
|        3 | C  | qwerty             |
|        4 | D  | asdf               |
|        5 | E  | asdf               |
+----------+----+--------------------+

Or, you can map them like this:

+----------+----+--------------------+
| RowOrder | ID | SomeNonUniqueValue |
+----------+----+--------------------+
|        1 | B  | qwerty             |
|        2 | C  | qwerty             |
|        3 | A  | qwerty             |
|        4 | E  | asdf               |
|        5 | D  | asdf               |
+----------+----+--------------------+

It is still a valid mapping, because all three values of qwerty are the same. Neither of these mapping is "more correct" than another.

Obviously, if your MyTable wasn't empty before the INSERT, you need to select new rows only.

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

MS SQL output inserted with insert from select

Your syntax is a little wrong here.

You want:

INSERT INTO [someDB].[dbo].[OBJ] ( column1, column2, column3 )
OUTPUT inserted.ID
SELECT TOP 1
       590675,
       column2,
       column3
FROM [someDB].[dbo].[OBJ] WHERE ID = 317817
ORDER BY ...?; --You have a TOP 1, thus you really need an ORDER BY as well.

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

---

Related Topics