SQL Server insert return data from SP to temp table

kuuvgm7e 于 2024-01-05 发布在其他

关注(0)|答案(3)|浏览(133)

I have a stored proc, SP1, which executes and does select on one table. Now i have the need to insert that data in another table. I dont want to duplicate the code so i thought of inserting the data returned by the SP1 in the temp table so i can do some processing on it and save it.

I tried INSERT INTO #tmp; exec dbo.Sp1; but it gives me an error saying Invalid object name '#tmp'. . Isnt there a way i can create this table dynamically? Is there a better solution to this problem?

sql-server

来源：https://stackoverflow.com/questions/9895367/insert-return-data-from-sp-to-temp-table

3条答案

按热度按时间

6psbrbz91#

The temp table has to exist before you can use insert into exec.

This is not such a draw back as it first seems as any changes to the procedure result set will likely brake your code.

赞(0）回复(0）举报 2024-01-05

bjp0bcyl2#

first run this:

create proc MySelect
as
begin
  select 1 as myColumn1, 2 as mycolumn2
end

and then this:

create table #tmp(
col_1 int,
col_2 int)

insert into #tmp exec MySelect

select * from #tmp

赞(0）回复(0）举报 2024-01-05

wlsrxk513#

If you have control over dbo.sp1 then the easiest way is to use a temp table. Declare the temp table in the parent proc and use it in the child proc (without declaring it).

The table will maintain scope through both and you wont have to worry about multiple users running it since temp tables are local.

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server insert return data from SP to temp table

3条答案

相关问题

热门标签

最新问答