SQL Server:从两个表中选择并插入一个表中

本文介绍了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