How to get all id's from inserted table

Question

I am insert bulk of records how to get all those Id's from inserted tables.

I want to use all those ids as forgein keys and insert into another table

INSERT INTO [dbo].[BudCustomers]
           ([LegalName]
           ,[EffectiveDate]
           ,[LawsonCustomerNumber]
           ,[ChangeReason]
           ,[ImportedRecord]
           ,[VersionID]
           ,[StatusID]
           ,[CreatedDate]
           ,[CreatedUserID]
           ,[LastModifiedDate]
           ,[LastModifiedUserID]
           ,[CustomerGroupID])
SELECT CustomerName
         ,'1900-01-01 00:00:00.000'
         , CASE WHEN PATINDEX('%[0-9]%', CustomerName) > 0 
            THEN REPLACE(SUBSTRING(CustomerName, PATINDEX('%[0-9]%', CustomerName),
            LEN(CustomerName)), ')', '') 
            ELSE 0 END
         ,''
         ,1
         ,1
         ,1
         ,GETDATE()
         ,'Import'
         ,GETDATE()
         ,'Import'
         ,NULL
FROM External_Blk_Itm_Contracts
WHERE TerminalName NOT IN (SELECT MBFTERMINALNAME FROM budterminals)

I assume these IDs are identity values? The best way to do that is using the OUTPUT clause. msdn.microsoft.com/en-us/library/ms177564.aspx — Sean Lange
INSERT INTO ... OUTPUT col_name INTO #temp_table SELECT ... — Lukasz Szozda
Look at the documentation I linked. It explains how this works quite clearly. — Sean Lange

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2015-11-05T17:01:41

Use OUTPUT clause:

CREATE TABLE #temp (CustomerId <datatype> );

INSERT INTO [dbo].[BudCustomers]
           ([LegalName]
           ,[EffectiveDate]
           ,[LawsonCustomerNumber]
           ,[ChangeReason]
           ,[ImportedRecord]
           ,[VersionID]
           ,[StatusID]
           ,[CreatedDate]
           ,[CreatedUserID]
           ,[LastModifiedDate]
           ,[LastModifiedUserID]
           ,[CustomerGroupID])
OUTPUT inserted.CustomerId
INTO #temp
SELECT CustomerName
         ,'1900-01-01 00:00:00.000'
         , CASE 
           WHEN PATINDEX('%[0-9]%', CustomerName) > 0 
            THEN REPLACE(SUBSTRING(CustomerName, PATINDEX('%[0-9]%', CustomerName),
            LEN(CustomerName)), ')', '')  ELSE 0 END
         ,''
         ,1
         ,1
         ,1
         ,GETDATE()
         ,'Import'
         ,GETDATE()
         ,'Import'
         ,NULL
FROM External_Blk_Itm_Contracts
WHERE TerminalName NOT IN (SELECT MBFTERMINALNAME FROM budterminals)

SELECT *
FROM #temp;

How to get all id's from inserted table

1 Answers