CODEWITHSUNDEEP
sqlsql-servert-sql
user1030181
user1030181

Reputation: 2015

How to get all id's from inserted table

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)

Upvotes: 0

Views: 132

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175826

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;

Upvotes: 3

Related Questions