How do I do a SELECT * INTO [temp table] FROM [stored procedure]
? Not FROM [Table]
and without defining [temp table]
?
Select
all data from BusinessLine
into tmpBusLine
works fine.
select *
into tmpBusLine
from BusinessLine
I am trying the same, but using a stored procedure
that returns data, is not quite the same.
select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'
Output message:
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'exec'.
I have read several examples of creating a temporary table with the same structure as the output stored procedure, which works fine, but it would be nice to not supply any columns.
Does your stored procedure only retrieve the data or modify it too? If it's used only for retrieving, you can convert the stored procedure into a function and use the Common Table Expressions (CTEs) without having to declare it, as follows:
However, whatever needs to be retrieved from the CTE should be used in one statement only. You cannot do a
with temp as ...
and try to use it after a couple of lines of SQL. You can have multiple CTEs in one statement for more complex queries.For example,
I met the same problem and here is what I did for this from Paul's suggestion. The main part is here is to use
NEWID()
to avoid multiple users run the store procedures/scripts at the same time, the pain for global temporary table.Quassnoi put me most of the way there, but one thing was missing:
****I needed to use parameters in the stored procedure.****
And OPENQUERY does not allow for this to happen:
So I found a way to work the system and also not have to make the table definition so rigid, and redefine it inside another stored procedure (and of course take the chance it may break)!
Yes, you can dynamically create the table definition returned from the stored procedure by using the OPENQUERY statement with bogus varaiables (as long the NO RESULT SET returns the same number of fields and in the same position as a dataset with good data).
Once the table is created, you can use exec stored procedure into the temporary table all day long.
And to note (as indicated above) you must enable data access,
Code:
Thanks for the information which was provided originally... Yes, finally I do not have to create all these bogus (strict) table defintions when using data from another stored procedure or database, and yes you can use parameters too.
Search reference tags:
SQL 2005 stored procedure into temp table
openquery with stored procedure and variables 2005
openquery with variables
execute stored procedure into temp table
Update: this will not work with temporary tables so I had to resort to manually creating the temporary table.
Bummer notice: this will not work with temporary tables, http://www.sommarskog.se/share_data.html#OPENQUERY
Reference: The next thing is to define LOCALSERVER. It may look like a keyword in the example, but it is in fact only a name. This is how you do it:
To create a linked server, you must have the permission ALTER ANY SERVER, or be a member of any of the fixed server roles sysadmin or setupadmin.
OPENQUERY opens a new connection to SQL Server. This has some implications:
The procedure that you call with OPENQUERY cannot refer temporary tables created in the current connection.
The new connection has its own default database (defined with sp_addlinkedserver, default is master), so all object specification must include a database name.
If you have an open transaction and are holding locks when you call OPENQUERY, the called procedure can not access what you lock. That is, if you are not careful you will block yourself.
Connecting is not for free, so there is a performance penalty.
Another method is to create a type and use PIPELINED to then pass back your object. This is limited to knowing the columns however. But it has the advantage of being able to do:
Code
I hope this helps. Please qualify as appropriate.
I would do the following
Create (convert SP to) a UDF (Table value UDF).
select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'