1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Running Rabbit
  • 2,634
  • 15
  • 48
  • 69
  • possible duplicate of [SQL Server Table definition from stored procedure result set](http://stackoverflow.com/questions/2477921/sql-server-table-definition-from-stored-procedure-result-set). I'd seriously question why you don't know the structure of the result set though. – Damien_The_Unbeliever Jun 27 '12 at 10:03

2 Answers2

1

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).

Romil Kumar Jain
  • 20,239
  • 9
  • 63
  • 92
  • The resolution your provided will work with the existing table, but not with the stored proc result set. My store proc contains the dynamic query – Running Rabbit Jun 27 '12 at 10:12
1

use one of the Rowset Functions: 

SELECT  *
INTO    #Temp
FROM    OPENQUERY(SERVERNAME, 'EXEC pr_StorProcName')
Vadim Tychonoff
  • 801
  • 5
  • 7
  • Doesn't this require adding a linked server pointing to itself? – Andriy M Jun 27 '12 at 10:49
  • What does ServerName means? It is giving me an error when I am providing my ServerName: Below are the syntax that I used: SELECT * INTO #Temp FROM OPENQUERY(DEVEXSQL, 'EXEC usp_naSearchV2 @UserName=''ssahu''') The error I am getting is: Could not find server 'DEVEXSQL' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. – Running Rabbit Jun 27 '12 at 10:59
  • Yes it is listed their. I think I would have to create the LinkedServer. Right?? – Running Rabbit Jun 27 '12 at 11:36
  • @Himanshu - SERVERNAME ss an identifier representing the name of the linked server run 'SP_LINKEDSERVERS' and make sure it's there under 'SRV_NAME' – Vadim Tychonoff Jun 27 '12 at 11:40