从另外两个表插入到第三个表中 [英] Inserting from two other tables into a third

问题描述

所以我试图一次将一小批行(<10)插入到一个表中.数据位于表值参数中，但我需要在插入之前向其中添加一些对所有行通用的额外列.我尝试了两种方法来做到这一点:

So I'm trying to insert a small batch of rows (<10) at once into a table. The data is in a table-valued parameter, but I need to add some extra columns to it that are common to all the rows before inserting it. I've tried two ways of doing this:

INSERT INTO endTable
([col1],[col2],[commoncol1],[commoncol2])
SELECT
t.col1, t.col2, @CommonValue1, @CommonValue2
FROM startTable t

我也尝试通过将公共变量放入临时表变量中，然后将其与 startTable 交叉连接来实现，如下所示:

I've also tried to do it by putting the common variables into a temporary table variable, and then cross joining it with startTable, like this:

DECLARE @tempTable TABLE([temp1],[temp2])
INSERT INTO @tempTable(temp1,temp2) VALUES (@CommonValue1, @CommonValue2)

INSERT INTO endTable
([col1],[col2],[commoncol1],[commoncol2])
SELECT
a.col1, a.col2, b.temp1, b.temp2
FROM startTable a CROSS JOIN @tempTable b

当且仅当 startTable 具有多行时，这两种尝试都会给出相同的错误:

Both of these attempts give the same error if and only if startTable has multiple rows:

子查询返回了 1 个以上的值.这在以下情况下是不允许的子查询跟随 =, !=, <, <= , >, >= 或当子查询用作一个表达式.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

我希望不用循环就能完成这些插入，但我对 SQL 并不是非常熟悉，所以我不知道是我运气不好，还是我遗漏了一些明显的东西.任何帮助将不胜感激.

I was hoping to accomplish these inserts without resorting to a loop, but I'm not incredibly familiar with SQL, so I don't know if I'm out of luck, or if I'm missing something obvious. Any help would be greatly appreciated.

推荐答案

由于您的 INSERT 语句没有子查询，因此错误消息没有意义.我的猜测是您在 endTable 上有一个引发错误的触发器.

Since your INSERT statement does not have a subquery, the error message doesn't make sense. My guess is that you have a trigger on endTable that is throwing the error.

运行以下查询以查看 endTable 上是否有任何触发器.如果这样做，则需要检查触发器中的 SQL 以了解它抛出错误的原因.

Run the following query to see if you have any triggers on endTable. If you do, then you will need to inspect the SQL in the trigger to see why it is throwing the error.

SELECT * 
FROM sys.triggers 
WHERE parent_id = OBJECT_ID(N'endTable')

这篇关于从另外两个表插入到第三个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！