snippetsqlMinor
Create Temp Table From Stored Procedure Result
Viewed 0 times
storedresultcreatetempprocedurefromtable
Problem
Is it possible to create a temp table from a stored procedure result set without having to specify column datatypes? If not, is it possible to create a physical table at least from the SP? The reason I am asking is because I have an SP that returns too many columns for me to declare in a create table command.
Is there something like
create table from
where columns do not need to specified and sql automaticly determines column attributes??
I am running SQL server 2008
Is there something like
create table from
where columns do not need to specified and sql automaticly determines column attributes??
I am running SQL server 2008
Solution
See following question from stack overflow. According to this question you can use
CREATE PROCEDURE getDatabaseList
AS
BEGIN
SELECT * FROM sys.databases
END
SELECT * INTO #MyTempTable FROM
OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;','EXEC getDatabaseList')
-- create temp table as usual, select , update or drop tableCode Snippets
CREATE PROCEDURE getDatabaseList
AS
BEGIN
SELECT * FROM sys.databases
END
SELECT * INTO #MyTempTable FROM
OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;','EXEC getDatabaseList')
-- create temp table as usual, select , update or drop tableContext
StackExchange Database Administrators Q#37048, answer score: 4
Revisions (0)
No revisions yet.