Получение личности из Insert Into/Select

Я работаю над сценарием в SQL Server 2012 (в качестве серверной части классической страницы ASP), чтобы выбрать все строки из моей исходной таблицы (aaa_test_ap) и распределить их среди трех других моих таблиц (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'))

sql-server asp-classic output insert-into scope-identity
person buck1112    schedule 18.09.2015    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы присоединяетесь к таблице, где находятся новые идентификаторы.

В качестве примера для второй вставки:

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, вероятно, есть только одна строка, поэтому это не должно создавать больше строк, чем ожидалось.

person Gordon Linoff    schedule 18.09.2015
comment
Будет ли CROSS JOIN производить декартово произведение? Какие преимущества будут у CROSS JOIN перед INNER JOIN? - person buck1112; 22.09.2015
comment
@buck1112 . . . Похоже, это и есть цель ваших запросов. Но ваша вставка, вероятно, генерирует только один id, поэтому декартово произведение не выйдет из-под контроля. - person Gordon Linoff; 25.09.2015
comment
Обновление: я применил это к своим большим тестовым таблицам, и теперь я получаю то, что кажется декартовым произведением: первая таблица (которая генерирует выходные идентификаторы) имеет 1768 затронутых строк, а каждая другая таблица имеет 3125824 (1768 в квадрате). ) затронуты строки. Это по-прежнему все вставки, без данных, существующих в таблицах до вставки. Что было бы хорошим шагом для исследования причины этого нежелательного результата? Спасибо - person buck1112; 09.10.2015