Какие столбцы можно использовать в разделе OUTPUT INTO?
Я пытаюсь создать таблицу сопоставления, чтобы связать идентификаторы новых строк в таблице с теми, из которых они скопированы. Предложение OUTPUT INTO кажется идеальным для этого, но, похоже, оно не ведет себя в соответствии с документацией.
Мой код:
DECLARE @Missing TABLE (SrcContentID INT PRIMARY KEY )
INSERT INTO @Missing
( SrcContentID )
SELECT cshadow.ContentID
FROM Private.Content AS cshadow
LEFT JOIN Private.Content AS cglobal ON cshadow.Tag = cglobal.Tag
WHERE cglobal.ContentID IS NULL
PRINT 'Adding new content headers'
DECLARE @Inserted TABLE (SrcContentID INT PRIMARY KEY, TgtContentID INT )
INSERT INTO Private.Content
( Tag, Description, ContentDate, DateActivate, DateDeactivate, SortOrder, CreatedOn, IsDeleted, ContentClassCode, ContentGroupID, OrgUnitID )
OUTPUT cglobal.ContentID, INSERTED.ContentID INTO @Inserted (SrcContentID, TgtContentID)
SELECT Tag, Description, ContentDate, DateActivate, DateDeactivate, SortOrder, CreatedOn, IsDeleted, ContentClassCode, ContentGroupID, NULL
FROM Private.Content AS cglobal
INNER JOIN @Missing AS m ON cglobal.ContentID = m.SrcContentID
Результаты в сообщении об ошибке:
Msg 207, Level 16, State 1, Line 34
Invalid column name 'SrcContentID'.
(строка 34 является той, у которой есть выход OUTPUT INTO)
Экспериментация предполагает, что в OUTPUT INTO могут быть выбраны только строки, которые действительно присутствуют в объекте INSERT. Но это противоречит документам в книгах в Интернете. В статье о
ВЫВОДЕ приведен пример E, описывающий аналогичное использование:
Предложение OUTPUT INTO возвращает значения из обновляемой таблицы (WorkOrder), а также из Продукта Таблица. Таблица продуктов используется в предложение FROM, чтобы указать строки для обновление.
Кто-нибудь работал с этой функцией?
(Тем временем я переписал свой код, чтобы выполнить задание, используя цикл курсора, но это уродливо, и мне все еще интересно)
Ответы
Ответ 1
Я проверил, что проблема в том, что вы можете использовать только столбцы INSERTED
. Документация, похоже, указывает, что вы можете использовать from_table_name
, но я не могу заставить ее работать (идентификатор многочастности "m.ContentID" не может быть связан.):
TRUNCATE TABLE main
SELECT *
FROM incoming
SELECT *
FROM main
DECLARE @Missing TABLE (ContentID INT PRIMARY KEY)
INSERT INTO @Missing(ContentID)
SELECT incoming.ContentID
FROM incoming
LEFT JOIN main
ON main.ContentID = incoming.ContentID
WHERE main.ContentID IS NULL
SELECT *
FROM @Missing
DECLARE @Inserted TABLE (ContentID INT PRIMARY KEY, [Content] varchar(50))
INSERT INTO main(ContentID, [Content])
OUTPUT INSERTED.ContentID /* incoming doesn't work, m doesn't work */, INSERTED.[Content] INTO @Inserted (ContentID, [Content])
SELECT incoming.ContentID, incoming.[Content]
FROM incoming
INNER JOIN @Missing AS m
ON m.ContentID = incoming.ContentID
SELECT *
FROM @Inserted
SELECT *
FROM incoming
SELECT *
FROM main
По-видимому, префикс from_table_name
разрешен только на DELETE
или UPDATE
(или MERGE
в 2008 году) - я не уверен, почему:
Префикс столбца, который указывает таблицу, включенную в предложение FROM
оператора DELETE
или UPDATE
, который используется для указания строк для обновления или удаления.
Если измененная таблица также указана в предложении FROM
, любая ссылка на столбцы в этой таблице должна быть присвоена префиксом INSERTED
или DELETED
.
Ответ 2
Вы можете сделать это с помощью MERGE на Sql Server 2008. Пример кода ниже:
--drop table A
create table A (a int primary key identity(1, 1))
insert into A default values
insert into A default values
delete from A where a>=3
-- insert two values into A and get the new primary keys
MERGE a USING (SELECT a FROM A) AS B(a)
ON (1 = 0) -- ignore the values, NOT MATCHED will always be true
WHEN NOT MATCHED THEN INSERT DEFAULT VALUES -- always insert here for this example
OUTPUT $action, inserted.*, deleted.*, B.a; -- show the new primary key and source data
Результат
INSERT, 3, NULL, 1
INSERT, 4, NULL, 2
то есть. для каждой строки новый первичный ключ (3, 4) и старый (1, 2). Создание таблицы под названием, например. #OUTPUT и добавление "INTO #OUTPUT;" в конце предложения OUTPUT сохраняются записи.
Ответ 3
Я столкнулся с ТОЧКО той же проблемой, что и вы, я чувствую вашу боль...
Насколько мне удалось выяснить, нет способа использовать префикс from_table_name с инструкцией INSERT.
Я уверен, что для этого есть жизнеспособная техническая причина, и я хотел бы точно знать, что это такое.
Хорошо, нашел это, здесь сообщение в форуме о том, почему это не работает:
Форумы MSDN
Ответ 4
Я думаю, что нашел решение этой проблемы, это печально связано с временной таблицей, но, по крайней мере, это предотвратит создание страшного курсора:)
Что вам нужно сделать, это добавить дополнительный столбец в таблицу, в которой вы дублируете записи, и дать ей тип "uniqueidentifer".
затем объявите временную таблицу:
DECLARE @tmptable TABLE (uniqueid uniqueidentifier, original_id int, new_id int)
вставьте данные в таблицу темпа следующим образом:
insert into @tmptable
(uniqueid,original_id,new_id)
select NewId(),id,0 from OriginalTable
сделайте настоящую вставку в исходную таблицу:
insert into OriginalTable
(uniqueid)
select uniqueid from @tmptable
Теперь, чтобы добавить вновь созданные значения идентичности в вашу временную таблицу:
update @tmptable
set new_id = o.id
from OriginalTable o inner join @tmptable tmp on tmp.uniqueid = o.uniqueid
Теперь у вас есть таблица поиска, которая содержит новый идентификатор и оригинальный идентификатор в одной записи, для вашего удовольствия:)
Надеюсь, это поможет кому-то...
Ответ 5
(MS) Если измененная таблица также указана в предложении FROM, любая ссылка на столбцы в этой таблице должна быть присвоена префиксом INSERTED или DELETED.
В вашем примере вы не можете использовать таблицу cglobal в OUTPUT, если это не INSERTED.column_name или DELETED.column_name:
INSERT INTO Private.Content
(Tag)
OUTPUT cglobal.ContentID, INSERTED.ContentID
INTO @Inserted (SrcContentID, TgtContentID)
SELECT Tag
FROM Private.Content AS cglobal
INNER JOIN @Missing AS m ON cglobal.ContentID = m.SrcContentID
Для меня работала простая таблица псевдонимов:
INSERT INTO con1
(Tag)
OUTPUT **con2**.ContentID, INSERTED.ContentID
INTO @Inserted (SrcContentID, TgtContentID)
SELECT Tag
FROM Private.Content con1
**INNER JOIN Private.Content con2 ON con1.id=con2.id**
INNER JOIN @Missing AS m ON con1.ContentID = m.SrcContentID