CODEWITHSUNDEEP
sql-serverstored-procedures
Running Rabbit
Running Rabbit

Reputation: 2720

Temporary table from stored procedure result in SQL Server

Can we create a temporary table from the stored procedure results dynamically?

I do not want to declare the temporary table columns manually. It shud take the schema of table from the stored procedure results.

Upvotes: 1

Views: 6866

Answers (2)

Vadim Tychonoff
Vadim Tychonoff

Reputation: 881

use one of the Rowset Functions: 

SELECT  *
INTO    #Temp
FROM    OPENQUERY(SERVERNAME, 'EXEC pr_StorProcName')

Upvotes: 1

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

Use following syntax template to create temp table on basis of result set.

Select * into #temptable from mytable


Select column1,column2,..columnn into #temptable from mytable

Notes:

The SELECT INTO statement is very fast, for one reason: the command isn't logged for backup purposes. More precisely, the command can be inside a transaction and any rollback command will correctly undo its effects. However, the new values aren't permanently stored in the log file, therefore after this command you can only perform a complete database backup (incremental backup raise errors). This explains why you have to explicitly enable this functionality for non-temporary tables (temporary tables are never included in backup, so you don't need to use the sp_dboption command before using SELECT INTO with a temporary table).

Upvotes: 1

Related Questions