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.
2 Solutions collect form web for “Temporary table from stored procedure result in SQL Server”
use one of the Rowset Functions:
SELECT * INTO #Temp FROM OPENQUERY(SERVERNAME, 'EXEC pr_StorProcName')
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
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).