SQL Server:从两个表中选择并插入一个表中 [英] SQL Server: Select from two tables and insert into one
本文介绍了SQL Server:从两个表中选择并插入一个表中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个带有两个表变量(@temp和@ temp2)的存储过程.
I have a stored procedure with two table variables (@temp and @temp2).
如何从两个临时表中选择值(两个表变量都包含一行)并将它们全部插入一个表中?
How can I select the values from both temp tables (Both table variables contain one row) and insert them all in one table ?
我尝试了以下操作,但此操作不起作用,并且出现错误,提示SELECT和INSERT语句的数量不匹配.
I tried the following but this didn't work and I got the error that the number of SELECT and INSERT statements does not match.
DECLARE @temp AS TABLE
(
colA datetime,
colB nvarchar(1000),
colC varchar(50)
)
DECLARE @temp2 AS TABLE
(
colD int
)
...
INSERT INTO MyTable
(
col1,
col2,
col3,
col4
)
SELECT colD FROM @temp2,
colA FROM @temp,
colB FROM @temp,
colC FROM @temp
非常感谢蒂姆的帮助.
推荐答案
由于两个表变量都只有一行,因此您可以交叉联接它们.
As both table variables have a single row you can cross join them.
INSERT INTO MyTable
(col1,
col2,
col3,
col4)
SELECT t.colA,
t.colB,
t.colC,
t2.colD
FROM @temp t
CROSS JOIN @temp2 t2
这篇关于SQL Server:从两个表中选择并插入一个表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文