How to Insert Multiple Records and Get the Identity Value

Insert multiple rows, get ids, and perform action on this ids

Here's an updated version based on feedback below.

The original version simply incremented OrderIds by 1, but they're in an IDENTITY field and auto-created. Original code/etc in DB_fiddle

Because of the identity field for orders, the logic is now as follows

• Identify any duplicates as above
• Create an appropriate number of orders for the above, and record the OrderIDs
• Update the relevant OrderIDs in table T1 (where the original rows had OrderType = 'Type 2')
• Update the Orders table with the relevant SomeProp1 and SomeProp2

Note it may be possible to remove one step (instead of inserting new orders, then updating them) but I'd want to be really careful that you could match the new orders to the relevant old orders.

The transaction is in there to help with isolating the changes; but you do need to be careful if running this process several times concurrently.

Here's a DB_Fiddle with the code below.

/*    DATA SETUP    */

CREATE TABLE #T1 (Id int, OrderID int, CategoryId int, OrderType nvarchar(50))
INSERT INTO #T1 (Id, OrderID, CategoryId, OrderType) VALUES
(1, 10, 15, N'Type1'), 
(2, 10, 15, N'Type2'), 
(3,  9, 17, N'Type1'), 
(4, 99, 17, N'Type2'), 
(5, 20, 25, N'Type1'), 
(6, 20, 25, N'Type2')

CREATE TABLE #Orders (Id int NOT NULL IDENTITY(1,1), SomeProp1 nvarchar(50), SomeProp2 nvarchar(50))

SET IDENTITY_INSERT #Orders ON;
INSERT INTO #Orders (Id, SomeProp1, SomeProp2) VALUES
( 9, N'test1', N'test2'),
(99, N'test1', N'test2'),
(10, N'test3', N'test4'), 
(20, N'test5', N'test6')
SET IDENTITY_INSERT #Orders OFF;


/*    WORKING TABLES    */

CREATE TABLE #OrderChanges (OCId_temp int IDENTITY(1,1), OrderId_new int)

CREATE TABLE #Dupes (DupesId_temp int IDENTITY(1,1), OrderID int, CategoryId int)


/*    PROCESSING    */

BEGIN TRY
    BEGIN TRANSACTION

    INSERT INTO #Dupes (OrderID, CategoryID)
        SELECT  OrderID, CategoryID 
        FROM    #T1 
        GROUP BY OrderID, CategoryID 
        HAVING  COUNT(*) > 1

    IF EXISTS(SELECT * FROM #Dupes)
        BEGIN

        -- Create appropriate number of new orders, to get IDs (blank for the moment)
        INSERT INTO #Orders (SomeProp1, SomeProp2)
            OUTPUT  inserted.Id
            INTO    #OrderChanges (OrderID_new)
            SELECT  NULL, NULL
            FROM    #Dupes

        -- Should now have same number of rows, with matching IDENTITY Ids, in #Dupes and #OrderChanges

        -- Update #T1
        UPDATE      T1
            SET     OrderId = OC.OrderID_new
            FROM    #T1 AS T1
                    INNER JOIN #Dupes AS Dupes ON T1.OrderID = Dupes.OrderID AND T1.CategoryId = Dupes.CategoryId
                    INNER JOIN #OrderChanges AS OC ON Dupes.DupesId_temp = OC.OCId_temp
            WHERE   T1.OrderType = N'Type2'

        -- Update Orders
        UPDATE      Orders
            SET     SomeProp1 = PrevOrders.SomeProp1,
                    SomeProp2 = PrevOrders.SomeProp2
            FROM    #Orders AS Orders
                    INNER JOIN #OrderChanges AS OC ON Orders.Id = OC.OrderId_new
                    INNER JOIN #Dupes AS Dupes ON OC.OCId_temp = Dupes.DupesId_temp
                    INNER JOIN #Orders AS PrevOrders ON Dupes.OrderID = PrevOrders.Id
    
        END
    
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH


/*    REPORTING AND WRAPUP     */

SELECT * FROM #T1 ORDER BY Id
SELECT * FROM #Orders ORDER BY Id

DROP TABLE #OrderChanges
DROP TABLE #Orders
DROP TABLE #T1
DROP TABLE #Dupes

Get Identity of multiple insertion in sql server 2008

Just use the OUTPUT clause - it can either return output to the application, or into a table variable for further work.

E.g. your query would be:

INSERT INTO tableA
OUTPUT inserted.ID
VALUES (1,2), (3,4), (4,5), ...

can SQL insert using select return multiple identities?

You can use the output clause.

Something like this if your identity column is named `IdentityCol' will return you id's as a result set.

INSERT california_authors (au_id, au_lname, au_fname)
OUTPUT inserted.IdentityCol
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

You can insert the id's to a table using output ... into.

Here is a sample that stores the id's in a table variable.

declare @IDs table (id int)

INSERT california_authors (au_id, au_lname, au_fname)
OUTPUT inserted.IdentityCol INTO @IDs
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

How can I insert multiple rows into a table and get all new identity valued in order?

Yes. the insert will always work, once you include the order by, the insert will be executed in that order.

Here I change the staging order, btw you dont need OUTPUT

SQL DEMO

insert #Staging (TrackingId, Value) values (201,1000),(204,2000),(203,2000),(202,1000);
                                             ^          ^          ^          ^

INSERT INTO #Target (Value <, otherfields>)
SELECT TrackingID <, otherfields>
FROM #Staging
ORDER BY TrackingID
;


SELECT *
FROM #Target;

Please read the comments below in that article the answer from the author:

• Could you elaborate on statement #4.

Yes, the identity values will be generated in the sequence established by the ORDER BY. If a clustered index exists on the identity column, then the values will be in the logical order of the index keys. This still doesn’t guarantee physical order of insertion. Index maintenance is a different step and that could also be done in parallel for example. So you could end up generating the identity values based on ORDER BY clause and then feeding those rows to the clustered index insert operator which will perform the maintenance task. You can see this in the query plan. You should really NOT think about physical operations or order but instead think of a table as a unordered set of rows. The index can be used to sort rows in logical manner (using ORDER BY clause) efficiently.

How to get inserted IDs of multiple rows on one INSERT?

MySQL & MariaDB have the LAST_INSERT_ID() function, and it returns the id generated by the most recent INSERT statement in your current session.

But when your INSERT statement inserts multiple rows, LAST_INSERT_ID() returns the first id in the set generated.

In such a batch of multiple rows, you can rely on the subsequent id's being consecutive. The MySQL JDBC driver depends on this, for example.

If the rows you insert include a mix of NULL and non-NULL values for the id column, you have a risk of messing up this assumption. The JDBC driver returns the wrong values for the set of generated id's.

SQL server-- Inserting multiple rows into a table with identity column

If you are on SQL Server 2005, use UNION ALL

INSERT STU (NAME,CLASS,SECTION) 
  SELECT 'A' AS NAME,'1' AS CLASS, 'A' AS SECTION
  UNION ALL
  SELECT 'B', '2', 'B'
  UNION ALL
  SELECT 'C', '3', 'C'

Versions later than 2005, you could use

INSERT STU (NAME,CLASS,SECTION) 
VALUES ('A','1','A'), 
       ('B','2','B'), ...  

Inserting into multiple tables with identity column

You could use @@ROWCOUNT here to determine if an update occurred within the last statement.

  INSERT INTO [dbo].[Remittances]
                           ([TransactionID],[RemittanceTransactionId],[InsertedBy],[InsertedDateTime],[SavedBy],[SavedDateTime])
               SELECT 
                            @TransactionID,[RemittanceTransactionId],[InsertedBy],[InsertedDateTime],[SavedBy],[SavedDateTime]
               FROM         @parRemittance;

               SET         @RemittanceID =  @@IDENTITY ;


     IF(@@ROWCOUNT=0) SET @RemittanceID = NULL

---

Related Topics