SQL 使用 select 语句合并插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14646585/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Merge insert with select statement
提问by
This works for me
这对我有用
MERGE Table1 AS tgt
USING
(
SELECT
TOP 1
*
FROM
Table2,
(
SELECT
itmid
FROM
Table3
WHERE
id = @id
) as a
WHERE
id = @id
) AS src
ON ( tgt.id = src.id )
WHEN MATCHED THEN
UPDATE SET qty = qty + @qty
WHEN NOT MATCHED THEN
INSERT itmid
VALUES itmid;
But when i change like this, its not working, showing error near last select
但是当我这样改变时，它不起作用，显示接近最后的错误 select
MERGE Table1 AS tgt
USING
(
SELECT
TOP 1
*
FROM
Table2
WHERE
id = @id
) AS src
ON ( tgt.id = src.id )
WHEN MATCHED THEN
UPDATE SET qty = qty + @qty
WHEN NOT MATCHED THEN
INSERT itmid
SELECT itmid FROM Table3 WHERE id = @id;
采纳答案by mipe34
According to MSDN docs
根据MSDN 文档
<merge_not_matched>::=
{
INSERT [ ( column_list ) ]
{ VALUES ( values_list )
| DEFAULT VALUES }
}
The syntax with INSERTfromSELECTlike:
INSERTfrom的语法SELECT如下：
WHEN NOT MATCHED THEN
INSERT itmid SELECT itmid FROM Table3 WHERE id=@id;
Is not allowed!
不被允许！
I would try to solve your problem doing another merge with Table3.
我会尝试通过与Table3.
回答by Nikshep
do this
做这个
MERGE Table1 AS tgt
USING (SELECT TOP 1 * FROM Table2
WHERE id = @id) AS src
ON (tgt.id = src.id)
WHEN MATCHED THEN UPDATE SET qty = qty + @qty
WHEN NOT MATCHED THEN
INSERT (itmid)
SELECT itmid FROM Table3 WHERE id=@id;
Should work fine according to http://msdn.microsoft.com/en-us/library/bb522522(v=sql.105).aspx
根据http://msdn.microsoft.com/en-us/library/bb522522(v=sql.105).aspx应该可以正常工作
Sorry I interpreted it wrong and my original answer would stand which was
对不起，我解释错了，我原来的答案是
MERGE Table1 AS tgt
USING (SELECT TOP 1 * FROM Table2
WHERE id = @id) AS src
ON (tgt.id = src.id)
WHEN MATCHED THEN UPDATE SET qty = qty + @qty
WHEN NOT MATCHED THEN
INSERT (itmid) values (SELECT top 1 itmid FROM Table3 WHERE id=@id);
As merge insert takes one insert at a time and it might fail for multiple inserts.
由于合并插入一次只进行一次插入，因此多次插入可能会失败。
