使用主键、外键重复插入

Repeated inserts with Primary key, foreign key(使用主键、外键重复插入)

问题描述

谁能告诉我如何使用主键和外键在两个表上重复多次插入这就是我所做的.这是需要完成的工作的一小部分.StatusTable 大约有 200 行.我正在尝试将此状态表的详细信息拆分为 2-表 1、表 2.

Can anyone tell me how to do repeated multiple inserts on two tables with primary key, foreign key Here's what I've done. This is a very snippet of what needs to be done. StatusTable has around 200 rows. I am trying to split the details of this Status table into 2- Table1, Table2.

在将每条记录插入到 Table1 之后，我得到了 Identity 列，这需要用一些额外的东西插入到 Table2 中.因此，如果 StatusTable 中有 200 行，则 Table1、Table2 中有 200 行.

After inserting each record into Table1, I am getting the Identity column and this needs to be inserted into Table2 with some additional stuff. So if there are 200 rows in StatusTable there are 200 in Table1, Table2.

但这不是它的工作方式.它将所有 200 行插入到 Table1，然后获取标识，然后将一行插入到 Table2.我知道它为什么这样做.但不知道如何解决它..

But thats not the way it is working. It is inserting all the 200 rows into Table1, then getting the Identity and then inserting a single row into Table2. I know why it is doing this. But not sure how to fix it..

     INSERT INTO [dbo].[Table1]
               ([UserID],  
               ,[FirstName].......)
     SELECT 'User1' AS [UserID]
               ,'FirstName'
     FROM [dbo].[StatusTable]

     SELECT @id =  SCOPE_IDENTITY()

     INSERT INTO [dbo].[Table2]
                ([AccountID],[Status]
           values (@id, 'S')

请推荐

推荐答案

使用 OUTPUT 子句

Use the OUTPUT clause

 DECLARE @IDS TABLE (id INT) 

 INSERT INTO [dbo].[Table1]
               ([UserID]  
               ,[FirstName])
     OUTPUT inserted.id INTO @IDS          
     SELECT 'User1' AS [UserID]
               ,'FirstName'
     FROM [dbo].[StatusTable]

     INSERT INTO [dbo].[Table2]
                ([AccountID],[Status])
         SELECT Id, 'S' FROM @IDS

这篇关于使用主键、外键重复插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！