Using Insert Into with a Stored Procedure

[lawlerpat]

I would like to use the "Select Into" syntax to populate a temptable but instead of using a select statement in the select into I would like to use an execute proc_name statement
Standard Example:
I know the following works

Select * into #myTable from some_real_table

What I want to do:

select * into #myTable from exec my_prop @myParam

The whole purpose in this is to aviod manually defining the temp table since the procedure my_prop might change over time and I would like the select into statement to keep up with the changes.

Thanks

 

[MAK9974]

create table xyz(id int, name char(60))

alter procedure xyz2 as
select [id],name from sysobjects

insert into xyz exec xyz2

 

[swampBoogie]

insert into #myTable exec my_prop @myParam

 

[DONNAJ]

select top 0 * into #thetemptab from abcdefg


insert into #thetemptab
select * from abcdefg

 

[lawlerpat]

Thank you both, but neither of your offers are what I am looking for.
I am looking for an option that will allow me to not define #myTable (don't want to write out a very large and sometimes changeing list of fields), and I "CAN'T" change the SP.

THanks,
patrick

 

[DONNAJ]

sorry I did not fully read your described reason

this may not give you enough space if your table is very large, but,

try this


declare @temptable varchar(8000)

exec @temptable = addthestuff --this is the proc name

select @temptable

 

[tlbroadbent]

You cannot use SELECT INTO to create and load data into a table from the Execution of a stored procedure. You must create the table before using INSERT #Table Exec SP. There is no way around this.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[lawlerpat]

Thank you,
Fianlly a definativie answer.

 

[tlbroadbent]

You're welcome. Glad to help.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm