Any suggestions on getting this to work? syntactically it is correct, and the individual sub-queries return a single result, however when combined the sub-queries do not return results to the insert statement. Essentially i am trying to insert these records into the table if they do not exist in the view.
有什么建议吗?从语法上来说,它是正确的,每个子查询返回一个结果,但是当合并子查询时,不会将结果返回到insert语句。实际上,如果视图中不存在这些记录,我将尝试将它们插入表中。
INSERT INTO PRG_T_BLK_MDL (BLK_ID, MDL_ID, GW, VE)
OUTPUT @@ROWCOUNT AS RC
SELECT (SELECT ID FROM PRG_T_BLK WHERE NAME=1),
(SELECT ID FROM PRG_T_MDL WHERE NAME='A'), 3500, 'AX'
FROM PRG_V_BLK_MDL
WHERE NOT EXISTS(SELECT 1 FROM PRG_V_BLK_MDL WHERE BLK=1 AND MDL='A' AND VER='AX')
1 个解决方案
#1
4
I am guessing that the OUTPUT
clause works (I haven't used it with @@ROWCOUNT
, but it seems ok).
我猜测输出子句是有效的(我还没有使用@@ROWCOUNT,但是它看起来很好)。
You don't need a FROM
clause -- at least not with the entire table. I might suggest:
您不需要FROM子句——至少不需要整个表。我建议你:
INSERT INTO PRG_T_BLK_MDL (BLK_ID, MDL_ID, GW, VE)
OUTPUT @@ROWCOUNT AS RC
SELECT (SELECT ID FROM PRG_T_BLK WHERE NAME = t.BLK),
(SELECT ID FROM PRG_T_MDL WHERE NAME = t.MDL), 3500, t.ver
FROM (SELECT 1 as BLK, 'A' as MDL, 'AX' as ver) t
WHERE NOT EXISTS (SELECT 1
FROM PRG_V_BLK_MDL v
WHERE v.BLK = t.BLK AND v.MDL = t.MDL AND v.ver = t.ver);
#1
4
I am guessing that the OUTPUT
clause works (I haven't used it with @@ROWCOUNT
, but it seems ok).
我猜测输出子句是有效的(我还没有使用@@ROWCOUNT,但是它看起来很好)。
You don't need a FROM
clause -- at least not with the entire table. I might suggest:
您不需要FROM子句——至少不需要整个表。我建议你:
INSERT INTO PRG_T_BLK_MDL (BLK_ID, MDL_ID, GW, VE)
OUTPUT @@ROWCOUNT AS RC
SELECT (SELECT ID FROM PRG_T_BLK WHERE NAME = t.BLK),
(SELECT ID FROM PRG_T_MDL WHERE NAME = t.MDL), 3500, t.ver
FROM (SELECT 1 as BLK, 'A' as MDL, 'AX' as ver) t
WHERE NOT EXISTS (SELECT 1
FROM PRG_V_BLK_MDL v
WHERE v.BLK = t.BLK AND v.MDL = t.MDL AND v.ver = t.ver);