blocks|key|362448|text|我已经验证了问题在于您只能使用INSERTED列。文档似乎表明您可以使用from_table_name，但我似乎无法让它工作(无法绑定由多部分组成的标识符"m.ContentID“)：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|362449|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|code-block|syntax|javascript|362450|显然，from_table_name前缀只允许在DELETE或UPDATE+(或2008年的MERGE+)上使用-我不确定为什么：|362451|362452|from_table_name|unordered-list-item|362453|362454|列前缀，用于指定包含在DELETE或UPDATE语句的FROM子句中的表，该语句用于指定要更新或删除的行。|362455|如果要修改的表也在FROM子句中指定，则必须用INSERTED或DELETED前缀限定对该表中列的任何引用。|362456|entityMap^0|F|8|10|F|0|0|3|F|O|6|V|6|1A|5|0|0|0|F|0|0|B|6|I|6|R|4|0|9|4|N|8|W|7|0^^$0|@$1|2|3|4|5|6|7|X|8|@$9|Y|A|Z|B|C]|$9|10|A|11|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|13|8|@$9|14|A|15|B|C]|$9|16|A|17|B|C]|$9|18|A|19|B|C]|$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]|$1|N|3|O|5|P|7|1D|8|@$9|1E|A|1F|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]|$1|R|3|S|5|6|7|1H|8|@$9|1I|A|1J|B|C]|$9|1K|A|1L|B|C]|$9|1M|A|1N|B|C]]|D|@]|E|$]]|$1|T|3|U|5|6|7|1O|8|@$9|1P|A|1Q|B|C]|$9|1R|A|1S|B|C]|$9|1T|A|1U|B|C]]|D|@]|E|$]]|$1|V|3|-4|5|6|7|1V|8|@]|D|@]|E|$]]]|W|$]]

I've verified that the problem is that you can only use <code>INSERTED</code> columns. The documentation seems to indicate that you can use <code>from_table_name</code>, but I can't seem to get it to work (The multi-part identifier "m.ContentID" could not be bound.):

<pre><code>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
</code></pre>

Apparently the <code>from_table_name</code> prefix is only allowed on <code>DELETE</code> or <code>UPDATE</code> (or <code>MERGE</code> in 2008) - I'm not sure why:

<ul>
<li><code>from_table_name</code></li>
</ul>

Is a column prefix that specifies a table included in the <code>FROM</code> clause of a <code>DELETE</code> or <code>UPDATE</code> statement that is used to specify the rows to update or delete.

If the table being modified is also specified in the <code>FROM</code> clause, any reference to columns in that table must be qualified with the <code>INSERTED</code> or <code>DELETED</code> prefix.

blocks|key|361772|text|我遇到了和你完全一样的问题，我感受到了你的痛苦...据我所知，没有办法在INSERT语句中使用from_table_name前缀。我相信这有一个可行的技术原因，我很想知道它到底是什么。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|361773|好了，找到了，这是一个论坛帖子，解释为什么它不能工作：MSDN+forums|offset|length|361774|entityMap|0|LINK|mutability|MUTABLE|url|http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/c1fe0b9c-9305-4cb0-9237-9374ff4fc03e/^0|0|R|B|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

I'm running into EXACTLY the same problem as you are, I feel your pain...
As far as I've been able to find out there's no way to use the from_table_name prefix with an INSERT statement.
I'm sure there's a viable technical reason for this, and I'd love to know exactly what it is.

Ok, found it, here's a forum post on why it doesn't work:
<a href="http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/c1fe0b9c-9305-4cb0-9237-9374ff4fc03e/" rel="nofollow noreferrer">MSDN forums</a>

blocks|key|3365427|text|我想我找到了这个问题的解决方案，不幸的是，它涉及到一个临时表，但至少它将防止创建可怕的游标:)您需要做的是向要从中复制记录的表中添加额外的列，并为其指定一个“唯一标识”类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3365428|然后声明一个临时表：|3365429|DECLARE+@tmptable+TABLE+(uniqueid+uniqueidentifier,+original_id+int,+new_id+int)|code-block|syntax|javascript|3365430|将数据插入临时表，如下所示：|3365431|insert+into+@tmptable
(uniqueid,original_id,new_id)
select+NewId(),id,0+from+OriginalTable|3365432|继续并对原始表执行真正的插入操作：|3365433|insert+into+OriginalTable
(uniqueid)
select+uniqueid+from+@tmptable|3365434|现在将新创建的标识值添加到临时表中：|3365435|update+@tmptable
set+new_id+=+o.id
from+OriginalTable+o+inner+join+@tmptable+tmp+on+tmp.uniqueid+=+o.uniqueid|3365436|现在您有了一个查找表，它将新的id和原始的id保存在一条记录中，以供您使用:)|3365437|我希望这对某些人有帮助。|3365438|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|17|8|@]|9|@]|A|$]]|$1|S|3|T|5|F|7|18|8|@]|9|@]|A|$G|H]]|$1|U|3|V|5|6|7|19|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

I think I found a solution to this problem, it sadly involves a temporary table, but at least it'll prevent the creation of a dreaded cursor :)
What you need to do is add an extra column to the table you're duplicating records from and give it a 'uniqueidentifer' type.

then declare a temporary table:

<pre><code>DECLARE @tmptable TABLE (uniqueid uniqueidentifier, original_id int, new_id int)
</code></pre>

insert the the data into your temp table like this:

<pre><code>insert into @tmptable
(uniqueid,original_id,new_id)
select NewId(),id,0 from OriginalTable
</code></pre>

the go ahead and do the real insert into the original table:

<pre><code>insert into OriginalTable
(uniqueid)
select uniqueid from @tmptable
</code></pre>

Now to add the newly created identity values to your temp table:

<pre><code>update @tmptable
set new_id = o.id
from OriginalTable o inner join @tmptable tmp on tmp.uniqueid = o.uniqueid
</code></pre>

Now you have a lookup table that holds the new id and original id in one record, for your using pleasure :)

I hope this helps somebody...

blocks|key|360501|text|(MS)如果要修改的表也在FROM子句中指定，则对该表中列的任何引用都必须使用INSERTED或DELETED前缀进行限定。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|360502|在您的示例中，您不能在输出中使用cglobal表，除非它是INSERTED.column_name或DELETED.column_name：|360503|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|code-block|syntax|javascript|360504|对我来说起作用的是一个简单的别名表，如下所示：|360505|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|360506|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

(MS) If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.

In your example, you can't use cglobal table in the OUTPUT unless it's INSERTED.column_name or DELETED.column_name:

<pre><code>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
</code></pre>

What worked for me was a simple alias table, like this:

<pre><code>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
</code></pre>

blocks|key|365191|text|您可以在Sql+Server2008中使用MERGE来完成此操作。示例代码如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|365192|--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|code-block|syntax|javascript|365193|结果是|365194|INSERT,+3,+NULL,+1
INSERT,+4,+NULL,+2|365195|即对于每一行，新的主键(3，4)和旧的主键(1，2)。例如，创建一个名为#OUTPUT的表，并在OUTPUT子句的末尾添加“INTO+#OUTPUT;”可以保存记录。|365196|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You can do this with a MERGE in Sql Server 2008. Example code below:

<pre><code>--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&gt;=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
</code></pre>

Result is

<pre><code>INSERT, 3, NULL, 1
INSERT, 4, NULL, 2
</code></pre>

i.e. for each row the new primary key (3, 4) and the old one (1, 2). Creating a table called e.g. #OUTPUT and adding " INTO #OUTPUT;" at the end of the OUTPUT clause would save the records.

I'm trying to build a mapping table to associate the IDs of new rows in a table with those that they're copied from. The OUTPUT INTO clause seems perfect for that, but it doesn't seem to behave according to the documentation. 

My code:

<pre><code>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
</code></pre>

Results in the error message:

<pre><code>Msg 207, Level 16, State 1, Line 34
Invalid column name 'SrcContentID'.
</code></pre>

(line 34 being the one with the OUTPUT INTO)

Experimentation suggests that only rows that are actually present in the target of the INSERT can be selected in the OUTPUT INTO. But this contradicts the docs in the books online. The article on OUTPUT Clause has example E that describes a similar usage:

<blockquote>
 The OUTPUT INTO clause returns values
 from the table being updated
 (WorkOrder) and also from the Product
 table. The Product table is used in
 the FROM clause to specify the rows to
 update.
</blockquote>

Has anyone worked with this feature?

(In the meantime I've rewritten my code to do the job using a cursor loop, but that's ugly and I'm still curious)

What columns can be used in OUTPUT INTO clause?

我正在尝试构建一个映射表，以便将表中新行的in与从中复制它们的in关联起来。OUTPUT INTO子句似乎非常适合这样做，但它的行为似乎与文档不符。我的代码：DECLARE @Missing TABLE (SrcContentID INT PRIMARY KEY )INSERT INTO @Missing     ( ...

问OUTPUT INTO子句中可以使用哪些列？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问OUTPUT INTO子句中可以使用哪些列？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问OUTPUT INTO子句中可以使用哪些列？
EN