Insert results of a Stored Procedure into a Temporary Table
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
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.
sql
sql-server
- asked 8 years ago
- G John
2Answer
You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.
CREATE PROC getBusinessLineHistory
AS
BEGIN
SELECT * FROM sys.databases
END
GO
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
'EXEC getBusinessLineHistory')
SELECT * FROM #MyTempTable
- answered 8 years ago
- Gul Hafiz
In SQL Server 2005 you can use INSERT INTO ... EXEC to insert the result of a stored procedure into a table. From MSDN's INSERT documentation (for SQL Server 2000, in fact):
--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
- answered 8 years ago
- Gul Hafiz
Your Answer