问题描述

我正在插入大量记录，如何从插入的表中获取所有这些 Id.

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

我想将所有这些 id 用作 forgein 键并插入到另一个表中

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)

推荐答案

使用 OUTPUT 子句:

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;

这篇关于如何从插入的表中获取所有ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！