SQL 从存储过程插入到表中选择结果集但列数不一样
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3525289/
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
Insert into Table select result set from stored procedure but column count is not same
提问by Dave
I need something like that which is of course not working.
我需要类似的东西，这当然不起作用。
insert into Table1
(
Id,
Value
)
select Id, value from
(
exec MySPReturning10Columns
)
I wanted to populate Table1 from result set returned by MySPReturning10Columns. Here the SP is returning 10 columns and the table has just 2 columns.
我想从返回的结果集中填充 Table1 MySPReturning10Columns。这里 SP 返回 10 列，表只有 2 列。
The following way works as long as table and result set from SP have same number of columns but in my case they are not same.
只要来自 SP 的表和结果集具有相同数量的列，以下方式就可以工作，但在我的情况下它们不相同。
INSERT INTO TableWith2Columns
EXEC usp_MySPReturning2Columns;
Also, I want to avoid adding "." as linked server just to make openquery and openrowset work anyhow.
另外，我想避免添加“。” 作为链接服务器只是为了使 openquery 和 openrowset 无论如何都能工作。
Is there a way not to have define table strucutre in temp table (all columns with datatypes and lenght)? Something like CTE.
有没有办法在临时表（所有具有数据类型和长度的列）中定义表结构？类似于 CTE。
回答by Andomar
You could use a temporary table as a go-between:
您可以使用临时表作为中间人：
insert into #TempTable exec MySP
insert into Table1 (id, value) select id, value from #TempTable
回答by Russ
You could solve the problem in two steps by doing the insert from the stored procedure into a temporary table, then do the insert selecting just the columns you want from the temporary table.
您可以通过将存储过程中的插入操作插入到临时表中，然后从临时表中仅选择您想要的列进行插入，从而分两步解决该问题。
Information on temporary tables: http://www.sqlteam.com/article/temporary-tables
回答by Andrei
-- Well, declare a temp table or a table var, depending on the number of rows expected -- from the SP. This table will be basically the result set of your SP.
-- 好吧，根据预期的行数，从 SP 声明一个临时表或一个表变量。该表将基本上是您的 SP 的结果集。
DECLARE @spResult AS TABLE
(
ID INT,
VALUE FLOAT,
....
);
-- Get the result set of the SP into the temp table.
-- 获取SP的结果集到临时表中。
INSERT @spResult EXEC STORED_PROC;
-- Now you can query the SP's result set for ID and Value;
-- 现在可以查询SP的ID和Value的结果集；
INSERT Table1 (ID, VALUE)
SELECT ID, VALUE FROM @spResult;
回答by Manivasagan
You dont need to create a temporary table, you can do it with single query by creating temporary view like this
您不需要创建临时表，您可以通过创建这样的临时视图来使用单个查询来完成
with tempView as EXEC MySPReturning10Columns insert into Table1 select id, value from tempView
The temporary view disappears as soon as the statement finishes execution
一旦语句完成执行，临时视图就会消失
