插入表从存储过程中选择结果集，但列数不同
我需要类似的东西，但这当然不起作用。
insert into Table1
(
Id,
Value
)
select Id, value from
(
exec MySPReturning10Columns
)
我想从 MySPReturning10Columns 返回的结果集中填充 Table1。这里 SP 返回 10 列，而表只有 2 列。
只要 SP 的表和结果集具有相同的列数，以下方法就可以工作，但在我的情况下，它们并不相同。
INSERT INTO TableWith2Columns
EXEC usp_MySPReturning2Columns;
另外，我想避免添加“。”作为链接服务器只是为了让 openquery 和 openrowset 工作无论如何。
有没有办法不在临时表中定义表结构（所有具有数据类型和长度的列）？类似 CTE 之类的东西。
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.
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.
INSERT INTO TableWith2Columns
EXEC usp_MySPReturning2Columns;
Also, I want to avoid adding "." as linked server just to make openquery and openrowset work anyhow.
Is there a way not to have define table strucutre in temp table (all columns with datatypes and lenght)? Something like CTE.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以使用临时表作为中间人：
You could use a temporary table as a go-between:
您可以分两步解决问题，方法是从存储过程插入临时表，然后从临时表中仅选择所需的列进行插入。
有关临时表的信息：http://www.sqlteam.com/article/temporary-tables
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
-- 好吧，根据预期的行数声明一个临时表或表变量
——来自 SP。该表基本上是 SP 的结果集。
-- 将SP的结果集获取到临时表中。
-- 现在可以查询SP的结果集的ID和Value；
-- 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.
-- Get the result set of the SP into the temp table.
-- Now you can query the SP's result set for ID and Value;
您不需要创建临时表，您可以通过创建临时视图来使用单个查询来完成此操作，
一旦语句执行完毕，临时视图就会消失
You dont need to create a temporary table, you can do it with single query by creating temporary view like this
The temporary view disappears as soon as the statement finishes execution