Можно ли для предложения вывода SQL вернуть столбец не вставляется?

Я внес некоторые изменения в свою базу данных, и мне нужно перенести старые данные в новые таблицы. Для этого мне нужно заполнить таблицу (ReportOptions), используя данные из исходной таблицы (Practice), и заполнить вторую промежуточную таблицу (PracticeReportOption).

ReportOption (ReportOptionId int PK, field1, field2...)
Practice (PracticeId int PK, field1, field2...)
PracticeReportOption (PracticeReportOptionId int PK, PracticeId int FK, ReportOptionId int FK, field1, field2...)

Я сделал запрос, чтобы получить все данные, необходимые для перехода от практики к ReportOptions, но у меня возникли проблемы с заполнением промежуточной таблицы.

--Auxiliary tables
DECLARE @ReportOption TABLE (PracticeId int /*This field is not on the actual ReportOption table*/, field1, field2...)
DECLARE @PracticeReportOption TABLE (PracticeId int, ReportOptionId int, field1, field2)

--First I get all the data I need to move
INSERT INTO @ReportOption
SELECT P.practiceId, field1, field2...
  FROM Practice P

--I insert it into the new table, but somehow I need to have the repation PracticeId / ReportOptionId
INSERT INTO ReportOption (field1, field2...)
OUTPUT @ReportOption.PracticeId, --> this is the field I don't know how to get
       inserted.ReportOptionId
  INTO @PracticeReportOption (PracticeId, ReportOptionId)
SELECT field1, field2
  FROM @ReportOption

--This would insert the relationship, If I knew how to get it!
INSERT INTO @PracticeReportOption (PracticeId, ReportOptionId)
SELECT PracticeId, ReportOptionId
  FROM @ReportOption

Если бы я мог сослаться на поле, которое отсутствует в таблице назначения в предложении OUTPUT, это было бы замечательно (я думаю, что не могу, но точно не знаю). Любые идеи о том, как удовлетворить мою потребность?

Заранее спасибо.

 marc_s08 июн. 2012 г., 15:36
Вы можете вернуть любой столбец таблицы, в которую вы вставили строку, в свойOUTPUT пункт. Таким образом, даже если вы не предоставите значение для данного столбца в вашемINSERT заявление, вы все еще можете указать этот столбец вOUTPUT пункт. Однако вы не можете возвращать переменные или столбцы SQL из других таблиц.
 Alejandro B.08 июн. 2012 г., 16:03
@marc_s спасибо за ваш ответ, но у меня нет нужного поля в таблице назначения (мне нужен PracticeId, которого нет в ReportOption)

Ответы на вопрос(2)

Решение Вопроса

Вы можете сделать это с помощьюMERGE вместо вставки:

так замени это

INSERT INTO ReportOption (field1, field2...)
OUTPUT @ReportOption.PracticeId, --> this is the field I don't know how to get
       inserted.ReportOptionId
  INTO @PracticeReportOption (PracticeId, ReportOptionId)
SELECT field1, field2
  FROM @ReportOption

с

MERGE INTO ReportOption USING @ReportOption AS temp ON 1 = 0
WHEN NOT MATCHED THEN
    INSERT (field1, field2)
    VALUES (temp.Field1, temp.Field2)
    OUTPUT temp.PracticeId, inserted.ReportOptionId, inserted.Field1, inserted.Field2
    INTO @PracticeReportOption (PracticeId, ReportOptionId, Field1, Field2);

Ключом является использование оператора, который никогда не будет истинным (1 = 0) в операторе слияния, поэтому вы всегда будете выполнять вставку, но иметь доступ к полям как в исходной, так и в целевой таблицах.

Вот весь код, который я использовал для его проверки:

CREATE TABLE ReportOption (ReportOptionID INT IDENTITY(1, 1), Field1 INT, Field2 INT)
CREATE TABLE Practice (PracticeID INT IDENTITY(1, 1), Field1 INT, Field2 INT)
CREATE TABLE PracticeReportOption (PracticeReportOptionID INT IDENTITY(1, 1), PracticeID INT, ReportOptionID INT, Field1 INT, Field2 INT)

INSERT INTO Practice VALUES (1, 1), (2, 2), (3, 3), (4, 4)


MERGE INTO ReportOption r USING Practice p ON 1 = 0
WHEN NOT MATCHED THEN
    INSERT (field1, field2)
    VALUES (p.Field1, p.Field2)
    OUTPUT p.PracticeId, inserted.ReportOptionId, inserted.Field1, inserted.Field2
    INTO PracticeReportOption (PracticeId, ReportOptionId, Field1, Field2);

SELECT  *
FROM    PracticeReportOption

DROP TABLE ReportOption
DROP TABLE Practice
DROP TABLE PracticeReportOption 

Больше чтения, и источник всего, что я знаю по этому вопросу,Вот

 Alejandro B.08 июн. 2012 г., 16:12
Спасибо, это делает трюк! Я собирался использовать поддельное временное поле, но это намного элегантнее.
 24 сент. 2013 г., 08:54
Отлично! Этот трюк - золотое зерно! Добавлено в первый ряд в коллекции!
 04 февр. 2015 г., 20:24
Это как раз то, что я искал, спасибо!
 04 мая 2018 г., 00:43
Ха! Я просто заново изобрел это решение, прежде чем увидел эту тему (и никогда ранее не использовал и даже не слышал оMerge Утверждение). Я пыталсяOutput мойInsertв таблицу сопоставления, которая включалаIdentity значение от целиTable с неInsert'значение изSelect - From Таблица. Без этого решения я полагаю, что я должен:Transactionб) получить дальшеIdentityс)Insert в темпTable с вычисленнымIdentity, г) установитьIdentity_Insertе)Insert в цельTable от временногоTableе) очиститьIdentity_Insert.
 09 янв. 2017 г., 20:55
Suweet! Я бы хотел, чтобы он не использовал случайно ошибочную команду MERGE, но в остальном это совершенно элегантно.

MS SQL Server 2005 or lower найдет этот ответ полезным.

MERGE будет работать только для SQL Server 2008 или выше. Для отдыха я нашел другой обходной путь, который даст вам возможность создавать таблицы отображения.

Вот как будет выглядеть Resolution для SQL 2005:

DECLARE @ReportOption TABLE (ReportOptionID INT IDENTITY(1, 1), Field1 INT, Field2 INT)
DECLARE @Practice TABLE(PracticeID INT IDENTITY(1, 1), Field1 INT, Field2 INT)
DECLARE @PracticeReportOption TABLE(PracticeReportOptionID INT IDENTITY(1, 1), PracticeID INT, ReportOptionID INT, Field1 INT, Field2 INT)

INSERT INTO @Practice (Field1, Field2) VALUES (1, 1)
INSERT INTO @Practice (Field1, Field2) VALUES (2, 2)
INSERT INTO @Practice (Field1, Field2) VALUES (3, 3)
INSERT INTO @Practice (Field1, Field2) VALUES (4, 4)

INSERT INTO @ReportOption (field1, field2)
    OUTPUT INSERTED.ReportOptionID, INSERTED.Field1, INSERTED.Field2 INTO @PracticeReportOption (ReportOptionID, Field1, Field2)
    SELECT Field1, Field2 FROM @Practice ORDER BY PracticeID ASC;


WITH CTE AS ( SELECT PracticeID, ROW_NUMBER() OVER ( ORDER BY PracticeID ASC ) AS ROW FROM @Practice )
UPDATE M SET M.PracticeID = S.PracticeID 
    FROM @PracticeReportOption AS M
    JOIN CTE AS S ON S.ROW = M.PracticeReportOptionID

    SELECT * FROM @PracticeReportOption

Основная хитрость заключается в том, что мы дважды заполняем таблицу сопоставления упорядоченными данными из таблицы источника и назначения. Для более подробной информации здесь:Слияние вставленных данных с использованием OUTPUT в SQL Server 2005

 04 мая 2018 г., 17:43
Это не решило бы мою проблему. Мне необходимоOutput мойInsertна выводTable это включает в себяIdentity значение от целиTable с неInsertзначение (ПК) из источникаTable (кстати, тогда я мог бы (в другой партии) использовать этот выводTable заселитьColumn в источникеTable сIdentity значение). БезMergeЯ полагаю, что мне нужно: а) начатьTransactionб) получить дальшеIdentityв) вставить в темпTable с вычисленнымIdentity, г) установитьIdentity_Insertе)Insert в цельTable от временногоTableе) очиститьIdentity_Insert.

Ваш ответ на вопрос