从插入/选择中获取身份

Question

我正在SQL Server 2012中的脚本(作为后端到经典ASP页面)从我的初始表(aaa_test_ap)中选择所有行，并将它们分布在我的3个其他表(aaa_test_users, aaa_test_users_positions和aaa_test_users_education)中。

我想获得aaa_test_users.ID的身份，一旦一行被插入到它，在其他两个表插入中使用，作为它们的FK (User_ID)，在同一查询中。

是否有可能在一个查询中获得INSERT INTO方法的身份?

我试过使用SCOPE_Identity()，但它只返回最后一个值。

对于OUTPUT方法，我将如何利用它生成的表值，以便将第一个插入语句生成的值插入到接下来的两个语句中，每个语句都在正确插入的行中?

首先，表:

CREATE TABLE [dbo].[aaa_test_sp]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) NULL,
    [first_name] [nvarchar](50) NULL,
    [last_name] [nvarchar](50) NULL,
    [position] [nvarchar](50) NULL,
    [phone] [nvarchar](50) NULL,
    [education] [nvarchar](50) NULL,
    [ListID] [int] NULL,
    CONSTRAINT [PK_aaa_test_sp] 
       PRIMARY KEY CLUSTERED ([ID] ASC)
 ) ON [PRIMARY]
 GO
CREATE TABLE [dbo].[aaa_test_users]
(
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) NULL,
    [first_name] [nvarchar](50) NULL,
    [last_name] [nvarchar](50) NULL,
    CONSTRAINT [PK_aaa_test_users] 
       PRIMARY KEY CLUSTERED ([UserID] ASC)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[aaa_test_users_positions]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NULL,
    [position] [nvarchar](50) NULL,
    [phone] [nvarchar](50) NULL,
    CONSTRAINT [PK_aaa_test_users_positions] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[aaa_test_users_education] 
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NULL,
    [education] [nvarchar](50) NULL,
    CONSTRAINT [PK_aaa_test_users_education] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO

这是我一直在做的查询:

--declare @NewUserID nvarchar(50)
DECLARE @InsertOutput1 table (UserID nvarchar(50));
--insert, first, rows from sp to users, and get the autoNumber'ed ID,
--"NewUserID"
INSERT INTO aaa_test_users (UserName, first_name, last_name)
OUTPUT inserted.UserID INTO @InsertOutput1
    SELECT 
        UserName, first_name, last_name
    FROM aaa_test_sp
    WHERE (ListId = '1')
--select * from @InsertOutput1
--SELECT SCOPE_IDENTITY() As NewUserID
--set @NewUserID=(SELECT SCOPE_IDENTITY() )
--now that the "NewUserID" has been generated,
--insert it, along with other columns,
--into the 'users_positions' table.
--print 'new user id is ' + @NewUserID
INSERT INTO aaa_test_users_positions (UserID, position, phone)
    (SELECT 
         @NewUserID, position, phone
     FROM aaa_test_sp
     WHERE (ListId = '1')
    )
--now that the "NewUserID" has been generated,
--insert it, along with other columns,
--into the 'users_education' table 
--print @NewUserID
INSERT INTO aaa_test_users_education (UserID, education)
   (SELECT @NewUserID, education
    FROM aaa_test_sp
    WHERE (ListId = '1'))

Answer 1

连接到新id所在的表

作为第二个插入的例子:

INSERT INTO aaa_test_users_positions(UserID, position, phone)
    SELECT io.UserID, position, phone
    FROM aaa_test_sp cross join
         @InsertOutput1 io
    WHERE ListId = '1';

注意:给定您的结构，@InsertOutput1中可能只有一行，所以这应该不会产生比预期更多的行。