By Ferdeen

2009-03-17 10:45:26 8 Comments

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

I am trying the same, but using a stored procedure that returns data, is not quite the same.

select *
into tmpBusLine
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.


@jmoreno 2018-07-10 14:58:39

Well, you do have to create a temp table, but it doesn't have to have the right schema....I've created a stored procedure that modifies an existing temp table so that it has the required columns with the right data type and order (dropping all existing columns, adding new columns):

create procedure #TempTableForSP(@tableId int, @procedureId int)  
    declare @tableName varchar(max) =  (select name  
                                        from tempdb.sys.tables 
                                        where object_id = @tableId
    declare @tsql nvarchar(max);    
    declare @tempId nvarchar(max) = newid();      
    set @tsql = '    
    declare @drop nvarchar(max) = (select  ''alter table tempdb.dbo.' + @tableName 
            +  ' drop column ''  + quotename( + '';''+ char(10)  
                                   from tempdb.sys.columns c   
                                   where c.object_id =  ' + 
                                         cast(@tableId as varchar(max)) + '  
                                   for xml path('''')  
    alter table tempdb.dbo.' + @tableName + ' add ' + QUOTENAME(@tempId) + ' int;
    exec sp_executeSQL @drop;    
    declare @add nvarchar(max) = (    
                                select ''alter table ' + @tableName 
                                      + ' add '' + name 
                                      + '' '' + system_type_name 
                           + case when d.is_nullable=1 then '' null '' else '''' end 
                                      + char(10)   
                              from sys.dm_exec_describe_first_result_set_for_object(' 
                               + cast(@procedureId as varchar(max)) + ', 0) d  
                                order by column_ordinal  
                                for xml path(''''))    

    execute sp_executeSQL  @add;    
    alter table '  + @tableName + ' drop column ' + quotename(@tempId) + '  ';      
    execute sp_executeSQL @tsql;  

create table #exampleTable (pk int);

declare @tableId int = object_Id('tempdb..#exampleTable')
declare @procedureId int = object_id('examplestoredProcedure')

exec #TempTableForSP @tableId, @procedureId;

insert into #exampleTable
exec examplestoredProcedure

Note this won't work if sys.dm_exec_describe_first_result_set_for_object can't determine the results of the stored procedure (for instance if it uses a temp table).

@Quassnoi 2009-03-17 10:50:44


INTO    #tmpTable

@Ferdeen 2009-03-17 10:54:45

Get a "Msg 208, Level 16, State 1, Line 1 Invalid object name 'tmpBusLine' (probably as it's not define up front).

@Quassnoi 2009-08-04 15:35:13

@Ferds: sorry, didn't understand your request at first. Updated with another solution.

@jcollum 2009-12-23 17:39:19

Great solution. One caveat, you'll need to enable 'DATA ACCESS' on your server: EXEC sp_serveroption 'TheServerName', 'DATA ACCESS', TRUE

@BraveNewMath 2013-05-06 18:35:56

You'll also need to allow remote access to the server. This will have security ramifications.

@Mr. TA 2013-08-23 17:13:50

Perfect. This works with existing linked servers and allows me to exec remote table valued functions, which is not supported directly.

@Sal 2017-01-23 16:13:17

This will not work if the target stored procedure uses temp tables

@Devansh 2013-10-05 04:03:38

  1. I'm creating a table with the following schema and data.
  2. Create a stored procedure.
  3. Now I know what the result of my procedure is, so I am performing the following query.

    CREATE TABLE [dbo].[tblTestingTree](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [ParentId] [int] NULL,
        [IsLeft] [bit] NULL,
        [IsRight] [bit] NULL,
        [Id] ASC
    ) ON [PRIMARY]
    SET IDENTITY_INSERT [dbo].[tblTestingTree] ON
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (1, NULL, NULL, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (2, 1, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (3, 1, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (4, 2, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (5, 2, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (6, 3, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (7, 3, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (8, 4, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (9, 4, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (10, 5, 1, NULL)
    SET IDENTITY_INSERT [dbo].[tblTestingTree] OFF

    VALUES (10, 5, 1, NULL) SET IDENTITY_INSERT [dbo].[tblTestingTree] On

    create procedure GetDate
        select Id,ParentId from tblTestingTree
    create table tbltemp
        id int,
        ParentId int
    insert into tbltemp
    exec GetDate
    select * from tbltemp;

@Sandeep Gaadhe 2016-02-26 11:05:06

If you're lucky enough to have SQL 2012 or higher, you can use dm_exec_describe_first_result_set_for_object

I have just edited the sql provided by gotqn. Thanks gotqn.

This creates a global temp table with name same as procedure name. The temp table can later be used as required. Just don't forget to drop it before re-executing.

    declare @procname nvarchar(255) = 'myProcedure',
            @sql nvarchar(max) 

    set @sql = 'create table ##' + @procname + ' ('
            select      @sql = @sql + '[' + + '] ' +  r.system_type_name + ','
            from        sys.procedures AS p
            cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
            where = @procname

            set @sql = substring(@sql,1,len(@sql)-1) + ')'
            execute (@sql)
            execute('insert ##' + @procname + ' exec ' + @procname)

@Gert Arnold 2018-05-11 11:35:14

Excellent! Just one remark: use sys.all_objects instead of sys.procedures if you want to do this for built-in stored procedures.

@Trubs 2018-06-20 03:15:06

This will also fail if the SP uses temporary tables within it. (but its pretty handy to have this as a proc in your arsenal)

@Tigerjz32 2015-03-13 19:38:34

Easiest Solution:

CREATE TABLE #temp (...);

EXEC [sproc];

If you don't know the schema then you can do the following. Please note that there are severe security risks in this method.

INTO #temp
                'EXEC [db].[schema].[sproc]')

@SHEKHAR SHETE 2016-07-06 11:47:18

if i dont know the column of returned resultset then??? i mean column may vary. so how to insert the result into temp table???

@Tigerjz32 2016-07-07 19:26:53

You could use OPENQUERY but it is not recommended as it comes with security flaws.

@Adriaan Davel 2016-07-20 09:12:35

"if i dont know the column of returned resultset then" then you cant use it in your logic. How will you use the data if you don't know what it is?

@Tigerjz32 2016-08-17 14:22:04

@AdriaanDavel I agree with you that you should always know your data (best practice), however what he might be saying is that there are times when the sproc returns dynamic columns and you don't always know what the schema will look like. In that case, you can use OPENROWSET to insert and create a table on the fly. However, there are obvious security risks with doing this...

@Danny Rancher 2017-10-26 09:43:12

SQLNCLI is invalid for me? how do you know that is the OLEDB provider?

@Tigerjz32 2017-10-26 16:31:12

What error are you seeing?

@nurettin 2017-10-31 08:18:37

There is no logic in saying "how will you use your data if you don't know the schema". Schema can be inferred from sys.columns. Even for temporary tables.

@Tigerjz32 2017-10-31 15:54:44

@nurettin sometimes you don't know what the stored procedure is going to return. What happens in that case? How could you create a temp table (when you don't know what the stored procedure will return) and insert into it from a stored procedure?

@nurettin 2017-11-01 18:36:34

@Tigerjz32 using openrowset, obviously.

@Tigerjz32 2017-11-02 19:18:40

@nurettin That makes no sense. You still need to know the schema don't you? What if you don't know the schema, what do you do?

@nurettin 2017-11-02 19:22:38

@Tigerjz32 as I said, SELECT * FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..#tmp');

@Tigerjz32 2017-11-02 19:23:41

@nurettin what do you do with that information? The question is, how do you insert a sproc's result into a table, given that you don't know what the sproc will return. How would you code that?

@nurettin 2017-11-02 19:40:20

@Tigerjz32 I'm not saying I would insert it into a table. Using might just mean showing it on a screen or summing the rows or completing missing data. It isn't always taking data from one table and putting it into another like you're used to.

@nurettin 2017-11-02 19:56:43

I suppose if you had to insert, you would match the column names and types to create an insert using the select I showed you with the target table.

@nurettin 2017-11-04 17:44:42

@Tigerjz32 I know what the question said, but I can't just let someone on the internet be wrong :-)

@Christian Loris 2009-08-04 17:11:38

This is an answer to a slightly modified version of your question. If you can abandon the use of a stored procedure for a user-defined function, you can use an inline table-valued user-defined function. This is essentially a stored procedure (will take parameters) that returns a table as a result set; and therefore will place nicely with an INTO statement.

Here's a good quick article on it and other user-defined functions. If you still have a driving need for a stored procedure, you can wrap the inline table-valued user-defined function with a stored procedure. The stored procedure just passes parameters when it calls select * from the inline table-valued user-defined function.

So for instance, you'd have an inline table-valued user-defined function to get a list of customers for a particular region:

CREATE FUNCTION CustomersByRegion 
    @RegionID int  
  FROM customers
  WHERE RegionID = @RegionID

You can then call this function to get what your results a such:

SELECT * FROM CustomersbyRegion(1)

Or to do a SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

If you still need a stored procedure, then wrap the function as such:

CREATE PROCEDURE uspCustomersByRegion 
    @regionID int  
     SELECT * FROM CustomersbyRegion(@regionID);

I think this is the most 'hack-less' method to obtain the desired results. It uses the existing features as they were intended to be used without additional complications. By nesting the inline table-valued user-defined function in the stored procedure, you have access to the functionality in two ways. Plus! You have only one point of maintenance for the actual SQL code.

The use of OPENROWSET has been suggested, but this is not what the OPENROWSET function was intended to be used for (From Books Online):

Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead.

Using OPENROWSET will get the job done, but it will incur some additional overhead for opening up local connections and marshalling data. It also may not be an option in all cases since it requires an ad hoc query permission which poses a security risk and therefore may not be desired. Also, the OPENROWSET approach will preclude the use of stored procedures returning more than one result set. Wrapping multiple inline table-value user-defined functions in a single stored procedure can achieve this.

@spencer7593 2010-08-11 18:29:57

+1 A table-valued function is an appropriate solution. We should make note of the minor drawbacks: the table-valued function is an extra database object, and it may be necessary to grant privileges on it.

@mrwaim 2011-03-04 16:49:51

Love the solution. One minor snag I hit, is that my table cant have order by where as it could have it in the stored procedure. Owh well, i'll sort it out

@mrwaim 2011-03-05 21:32:58

One more snag - "Cannot access temporary tables from within a function"

@greg 2013-04-16 20:16:57

The original question is how do we create a temp table w/ the results of the sp. This is a good pattern, but doesnt address this question

@Christian Loris 2013-05-28 12:51:18

greg, the first line in my answer states "This is an answer to a slightly modified version of your question." Your comment is redundant.

@gotqn 2015-06-03 11:47:50

In order to insert the first record set of a stored procedure into a temporary table you need to know the following:

  1. only the first row set of the stored procedure can be inserted into a temporary table
  2. the stored procedure must not execute dynamic T-SQL statement (sp_executesql)
  3. you need to define the structure of the temporary table first

The above may look as limitation, but IMHO it perfectly makes sense - if you are using sp_executesql you can once return two columns and once ten, and if you have multiple result sets, you cannot insert them into several tables as well - you can insert maximum in two table in one T-SQL statement (using OUTPUT clause and no triggers).

So, the issue is mainly how to define the temporary table structure before performing the EXEC ... INTO ... statement.

The first works with OBJECT_ID while the second and the third works with Ad-hoc queries as well. I prefer to use the DMV instead of the sp as you can use CROSS APPLY and build the temporary table definitions for multiple procedures at the same time.

SELECT, r.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;

Also, pay attention to the system_type_name field as it can be very useful. It stores the column complete definition. For, example:


and you can use it directly in most of the cases to create the table definition.

So, I think in most of the cases (if the stored procedure match certain criteria) you can easily build dynamic statements for solving such issues (create the temporary table, insert the stored procedure result in it, do what you need with the data).

Note, that the objects above fail to define the first result set data in some cases like when dynamic T-SQL statements are executed or temporary tables are used in the stored procedure.

@nahab 2016-09-22 14:12:37

practical observation on limitations: if you have to insert output of some sp (lets call it SP_LEVEL_0) to temp table dynamically created using above approach in another sp (lets call it SP_LEVEL_1), you cannot do the same trick for output of this SP_LEVEL_1 to some another temp table in SP_LEVEL_2

@Matthew Baker 2017-01-27 11:52:46

This can be done in SQL Server 2014+ provided SP only returns one table. If anyone finds a way of doing this for multiple tables I'd love to know about it.

DECLARE @storeProcname NVARCHAR(MAX) = ''

SET @storeProcname = 'myStoredProc'


SELECT ',' +name+' ' + system_type_name 
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storeProcname),0)
),1,1,'(') + ')'

EXEC (@strSQL)

EXEC ('myStoredProc @param1=1, @param2=2')

SELECT * FROM myTableName

DROP TABLE myTableName

This pulls the definition of the returned table from system tables, and uses that to build the temp table for you. You can then populate it from the SP as stated before.

There are also variants of this that work with Dynamic SQL too.

@ProblemSolver 2015-09-07 11:37:14

If the query doesn't contain parameter, use OpenQuery else use OpenRowset.

Basic thing would be to create schema as per stored procedure and insert into that table. e.g.:

                  RequisitionTypeSourceTypeID INT
                , RequisitionTypeID INT
                , RequisitionSourcingTypeID INT
                , AutoDistOverride INT
                , AllowManagerToWithdrawDistributedReq INT
                , ResumeRequired INT
                , WarnSupplierOnDNRReqSubmission  INT
                , MSPApprovalReqd INT
                , EnableMSPSupplierCounterOffer INT
                , RequireVendorToAcceptOffer INT
                , UseCertification INT
                , UseCompetency INT
                , RequireRequisitionTemplate INT
                , CreatedByID INT
                , CreatedDate DATE
                , ModifiedByID INT
                , ModifiedDate DATE
                , UseCandidateScheduledHours INT
                , WeekEndingDayOfWeekID INT
                , AllowAutoEnroll INT
EXEC [dbo].[usp_MySp] 726,3

@FistOfFury 2012-07-31 17:09:22

If the results table of your stored proc is too complicated to type out the "create table" statement by hand, and you can't use OPENQUERY OR OPENROWSET, you can use sp_help to generate the list of columns and data types for you. Once you have the list of columns, it's just a matter of formatting it to suit your needs.

Step 1: Add "into #temp" to the output query (e.g. "select [...] into #temp from [...]").

The easiest way is to edit the output query in the proc directly. if you can't change the stored proc, you can copy the contents into a new query window and modify the query there.

Step 2: Run sp_help on the temp table. (e.g. "exec tempdb..sp_help #temp")

After creating the temp table, run sp_help on the temp table to get a list of the columns and data types including the size of varchar fields.

Step 3: Copy the data columns & types into a create table statement

I have an Excel sheet that I use to format the output of sp_help into a "create table" statement. You don't need anything that fancy, just copy and paste into your SQL editor. Use the column names, sizes, and types to construct a "Create table #x [...]" or "declare @x table [...]" statement which you can use to INSERT the results of the stored procedure.

Step 4: Insert into the newly created table

Now you'll have a query that's like the other solutions described in this thread.

   --these columns were copied from sp_help
   COL1 INT,
   COL2 INT   

Exec spMyProc 

This technique can also be used to convert a temp table (#temp) to a table variable (@temp). While this may be more steps than just writing the create table statement yourself, it prevents manual error such as typos and data type mismatches in large processes. Debugging a typo can take more time than writing the query in the first place.

@lakshmivisalij 2015-03-24 09:01:55

If you know the parameters that are being passed and if you don't have access to make sp_configure, then edit the stored procedure with these parameters and the same can be stored in a ##global table.

@Hlin 2014-08-19 19:12:14

I would do the following

  1. Create (convert SP to) a UDF (Table value UDF).

  2. select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'

@yucer 2014-12-07 01:48:07

There could be some obstaclesto make your first step. By example if the the original SP use temporary tables. UDFs can not use temporary tables.

@Rashmi Pandit 2009-08-06 07:28:23

Does your stored procedure only retrieve the data or modify it too? If it's used only for retrieving, you can convert the stored procedure into a function and use the Common Table Expressions (CTEs) without having to declare it, as follows:

with temp as (
    select * from dbo.fnFunctionName(10, 20)
select col1, col2 from temp

However, whatever needs to be retrieved from the CTE should be used in one statement only. You cannot do a with temp as ... and try to use it after a couple of lines of SQL. You can have multiple CTEs in one statement for more complex queries.

For example,

with temp1020 as (
    select id from dbo.fnFunctionName(10, 20)
temp2030 as (
    select id from dbo.fnFunctionName(20, 30)
select * from temp1020 
where id not in (select id from temp2030)

@yucer 2014-12-03 13:48:57

These are not temporary tables, are CTEs.…

@Rashmi Pandit 2014-12-04 22:24:52

Thanks @yucer ... I believe I didn't know they were called CTEs back then :)

@zhongxiao37 2014-08-18 09:11:12

I met the same problem and here is what I did for this from Paul's suggestion. The main part is here is to use NEWID() to avoid multiple users run the store procedures/scripts at the same time, the pain for global temporary table.

DECLARE @sql varchar(max) = '', 
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'

EXEC('SELECT * FROM [' + @tmp_global_table + ']')

@StuartQ 2014-02-05 16:21:29

If the OPENROWSET is causing you issues, there is another way from 2012 onwards; make use of sys.dm_exec_describe_first_result_set_for_object, as mentioned here: Retrieve column names and types of a stored procedure?

First, create this stored procedure to generate the SQL for the temporary

CREATE PROCEDURE dbo.usp_GetStoredProcTableDefinition(
    @ProcedureName  nvarchar(128),
    @TableName      nvarchar(128),
    @SQL            nvarchar(max) OUTPUT
SET @SQL = 'CREATE TABLE ' + @tableName + ' ('

SELECT @SQL = @SQL + '['+name +'] '+ system_type_name +''  + ','
        FROM sys.dm_exec_describe_first_result_set_for_object

--Remove trailing comma
SET @SQL =  @SQL +')'

To use the procedure, call it in the following way:


exec dbo.usp_GetStoredProcTableDefinition
    @TableName='##YourGlobalTempTable',@SQL = @SQL OUTPUT

INSERT INTO ##YourGlobalTempTable
EXEC    [dbo].usp_YourProcedure

select * from ##YourGlobalTempTable

Note that I'm using a global temporary table. That's because using EXEC to run the dynamic SQL creates its own session, so an ordinary temporary table would be out of scope to any subsequent code. If a global temporary table is a problem, you can use an ordinary temporary table, but any subsequent SQL would need to be dynamic, that is, also executed by the EXEC statement.

@Trisped 2015-07-06 19:38:31

You forgot to create the table from @SQL.

@kevchadders 2009-08-06 07:49:38

I found Passing Arrays/DataTables into Stored Procedures which might give you another idea on how you might go solving your problem.

The link suggests to use an Image type parameter to pass into the stored procedure. Then in the stored procedure, the image is transformed into a table variable containing the original data.

Maybe there is a way this can be used with a temporary table.

@EndlessSpace 2011-06-10 19:24:47

This is no longer required in versions Sql2008 & later with the introduction of Table Value Parameters. Now you can directly pass a .net dataset or datatable object to a sql stored procedure with having to do conversion to byte as mentioned in the above link

@Gavin 2009-03-17 14:08:11

If you want to do it without first declaring the temporary table, you could try creating a user-defined function rather than a stored procedure and make that user-defined function return a table. Alternativly, if you want to use the stored procedure, try something like this:

   COL1 INT,

Exec SpGetRecords 'Params'

@Craig 2012-04-26 16:18:37

I think the point was to generate the schema without having to explicitly declare it.

@funkymushroom 2012-06-01 17:57:37

I would be interested to know what the difference between this and @Aaron Alton's solution above. This one seems far simpler, but I am unsure as to any other implications.

@whitneyland 2012-08-24 18:17:49

@funkymushroom - Yes there are implications to this. UDF's can't do certain things SPROCS can do like Common table expressions.

@Brady 2014-01-23 16:15:31

This will work but if you ever add additional columns to the SpGetRecords stored procedure, this will blow up.

@Matt Stephenson 2014-04-09 05:41:54

You only get one INSERT INTO EXEC per call stack. SpGetRecords and any other proc it calls may not use this strategy in their own code. This can surprise maintainers of SpGetRecords.

@ruffin 2015-04-02 13:43:54

@MattStephenson Is there a workaround without OPENROWSET for that situation (multiple/"hidden" INSERT INTO EXECs) that you've found?

@Matt Stephenson 2015-04-03 13:17:22

@ruffin I don't know another workaround. I've often picked the least complex SP and turned (part of) it into a TVF.

@ruffin 2015-04-03 14:08:36

Yeah, that's essentially what I ended up doing in a similar situation: sp_helptext the sproc's code and hack in what changes you need in a new, custom version. Thanks.

@NickG 2015-04-24 09:30:50

This doesn't answer the question at all and I don't see why it's so upvoted? The OP explicitly stated "without defining [temp table]" and your very first line has a create temp table statement.

@rexroxm 2017-01-23 12:04:36

This works fine. No permissions through sp_configure needed.

@briantyler 2011-09-26 16:04:54

This stored proc does the job:

CREATE PROCEDURE [dbo].[ExecIntoTable]
    @tableName          NVARCHAR(256),
    @storedProcWithParameters   NVARCHAR(MAX)
    DECLARE @driver         VARCHAR(10)
    DECLARE @connectionString   NVARCHAR(600)
    DECLARE @sql            NVARCHAR(MAX)
    DECLARE @rowsetSql      NVARCHAR(MAX)

    SET @driver = '''SQLNCLI'''

    SET @connectionString = 
        '''server=' + 
            CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) + 
            COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') + 

    SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + ''''

    SET @sql = '
    ' + @tableName + ' 
    OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'

    EXEC (@sql)

It's a slight rework of this: Insert stored procedure results into table so that it actually works.

If you want it to work with a temporary table then you will need to use a ##GLOBAL table and drop it afterwards.

@Matt Hamilton 2009-03-17 10:50:17

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

@Ferdeen 2009-03-17 10:53:29

This requires the authors_sales to be defined up front. I am trying to avoid this. Thanks.

@Matt Hamilton 2009-03-17 10:55:03

Ah right. Yeah, I think you may be out of luck in that case.

@Ferdeen 2009-03-17 10:57:14

I thought as much. So useful Inserting into tmp tables on the fly, but not so useful if you need to know the dataset structure returned from a stored proc. Thanks for you help.

@Rich Andrews 2009-03-17 11:07:18

@Even Mien 2016-04-11 17:34:56

To use the same schema, you can make a copy as follows: select top 0 * into tempTable from realTable (

@Aaron Alton 2009-08-04 15:27:34

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
    SELECT * FROM sys.databases

sp_configure 'Show Advanced Options', 1
sp_configure 'Ad Hoc Distributed Queries', 1

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

@Rob Farley 2009-08-05 13:24:36

This is the right way to do it. OPENROWSET is pretty much the only way to treat the results of a stored procedure as a table expression.

@Ferdeen 2009-08-10 12:18:10

This seems a bit cumbersome just to insert into a table. A lot of configuring to do. Also when I tried it I got "Msg 7357, Level 16, State 2, Line 1 Cannot process the object "EXEC GetPartyAnalysisData 146". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object." So you need to set a linked server...

@MartW 2009-08-11 12:30:45

You don't need a linked server, but you would need to get the connection string right...and also, specify the full path to the stored procedure including the database name and the sp's owner.

@MartW 2009-08-11 12:37:57

Indeed, that error you're getting when you try this is what I get when I try to access an object that isn't in the same database as the default db for the OPENROWSET connection (which is probably 'master').

@jcollum 2009-12-23 18:09:16

On 2nd thought, this solution worked better for me because setting server level settings with sp_serveroption is verboten around here.

@Tim Abell 2010-11-17 16:16:28

eeeeew! a reference to the same server? nasty. definitely more of a hack than having to manually create the temp table

@Alkampfer 2011-11-10 15:59:24

-it is cool, but it does not work if the stored procedure declare a temp table inside, I've tried with lots of stored procedure and everything is ok, but if I use a #tablename inside the stored I got a invalid object #tablename.

@Alpha 2012-03-13 20:57:27

What happens here if the SP returns more than one resultset?

@ErikE 2012-07-30 18:26:41

@Alkampfer put SET FMTONLY OFF; in your OPENQUERY call before the actual SP execution, that will make it work with temp tables. Just be aware that the SP will be run twice.

@greg 2013-04-16 20:20:10

i agree that this is a hack and should probably be avoided unless your back is against the wall. Changing the sp to a function is probably a better angle to take. IMHO.

@Jason Kleban 2015-06-22 20:28:38

Watch out for Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "..." is a duplicate.

@xpt 2016-05-17 20:34:34

What if my SP need parameters as well? E.g., Exec SpGetRecords 'Params1', 'Params2', how should I properly quote them?

@Francis Lord 2016-08-19 17:29:46

@greg One problem with changing a SP to a function is that functions have ore restrictions that SPs. There are stuff you can do in a SP that you can't in a function (e.g. call another SP, edit tables)

@dsz 2017-11-24 02:16:14

Bear in mind that the EXEC string has to be a literal string - you can't pass in a calculated string, which means you can't really pass parameters.

@Triynko 2018-03-22 17:46:17

OPENROWSET not supported in SQL Azure?

@nitin 2011-03-24 07:15:08

declare @temp table
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
INSERT @temp  Exec sp_helpfile;
select * from @temp;

@t.durden 2017-05-19 15:31:31

doesn't address OP original question, doing the insert with out defining the temp table first.

@SoftwareARM 2011-03-10 18:30:05


    col1 INT NOT NULL,
    col2 NCHAR(50) NOT NULL,
    col3 TEXT NOT NULL,
    col5 NCHAR(50) NULL,
    col6 CHAR(2) NULL,
    col6 NCHAR(100) NULL,
    col7 INT NULL,
    col8 NCHAR(50) NULL,

DECLARE @Para1 int
DECLARE @Para2 varchar(32)
DECLARE @Para3 varchar(100)
DECLARE @Para4 varchar(15)
DECLARE @Para5 varchar (12)
DECLARE @Para6 varchar(1)
DECLARE @Para7 varchar(1)

SET @Para1 = 1025
SET @Para2 = N'6as54fsd56f46sd4f65sd'
SET @Para3 = N'XXXX\UserName'
SET @Para4 = N''
SET @Para6 = N'X'
SET @Para7 = N'X'

EXEC [dbo].[usp_ProcedureName] @Para1, @Para2, @Para3, @Para4, @Para5, @Para6, @Para6

I hope this helps. Please qualify as appropriate.

@Doug Lubey of Louisiana 2010-02-11 19:39:46

Quassnoi put me most of the way there, but one thing was missing:

****I needed to use parameters in the stored procedure.****

And OPENQUERY does not allow for this to happen:

So I found a way to work the system and also not have to make the table definition so rigid, and redefine it inside another stored procedure (and of course take the chance it may break)!

Yes, you can dynamically create the table definition returned from the stored procedure by using the OPENQUERY statement with bogus varaiables (as long the NO RESULT SET returns the same number of fields and in the same position as a dataset with good data).

Once the table is created, you can use exec stored procedure into the temporary table all day long.

And to note (as indicated above) you must enable data access,



declare @locCompanyId varchar(8)
declare @locDateOne datetime
declare @locDateTwo datetime

set @locDateOne = '2/11/2010'
set @locDateTwo = getdate()

--Build temporary table (based on bogus variable values)
--because we just want the table definition and
--since openquery does not allow variable definitions...
--I am going to use bogus variables to get the table defintion.

select * into #tempCoAttendanceRpt20100211
  'EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"')

set @locCompanyId = '7753231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

set @locCompanyId = '9872231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

select * from #tempCoAttendanceRpt20100211
drop table #tempCoAttendanceRpt20100211

Thanks for the information which was provided originally... Yes, finally I do not have to create all these bogus (strict) table defintions when using data from another stored procedure or database, and yes you can use parameters too.

Search reference tags:

  • SQL 2005 stored procedure into temp table

  • openquery with stored procedure and variables 2005

  • openquery with variables

  • execute stored procedure into temp table

Update: this will not work with temporary tables so I had to resort to manually creating the temporary table.

Bummer notice: this will not work with temporary tables,

Reference: The next thing is to define LOCALSERVER. It may look like a keyword in the example, but it is in fact only a name. This is how you do it:

sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                   @provider = 'SQLOLEDB', @datasrc = @@servername

To create a linked server, you must have the permission ALTER ANY SERVER, or be a member of any of the fixed server roles sysadmin or setupadmin.

OPENQUERY opens a new connection to SQL Server. This has some implications:

The procedure that you call with OPENQUERY cannot refer temporary tables created in the current connection.

The new connection has its own default database (defined with sp_addlinkedserver, default is master), so all object specification must include a database name.

If you have an open transaction and are holding locks when you call OPENQUERY, the called procedure can not access what you lock. That is, if you are not careful you will block yourself.

Connecting is not for free, so there is a performance penalty.

@Contango 2017-07-01 13:43:11

If you don't know your server name, use SELECT @@SERVERNAME. You can also use EXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE

@pierre 2009-08-11 12:46:05

Another method is to create a type and use PIPELINED to then pass back your object. This is limited to knowing the columns however. But it has the advantage of being able to do:

FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type))

@dotjoe 2009-03-17 14:06:03

When the stored procedure returns a lot of columns and you do not want to manually "create" a temporary table to hold the result, I've found the easiest way is to go into the stored procedure and add an "into" clause on the last select statement and add 1=0 to the where clause.

Run the stored procedure once and go back and remove the SQL code you just added. Now, you'll have an empty table matching the stored procedure's result. You could either "script table as create" for a temporary table or simply insert directly into that table.

@Ian Roke 2009-08-27 15:24:20

+1, excellent suggestion. You could even add a quick optional variable to the sproc called @TableCreate or something similar that when is not null do the steps above. Doesn't require changing of the sproc then once it is set up.

@DotnetDude 2012-04-24 18:10:23

@dotjoe Do you do a SELECT INTO a temp table and do a script table as create from the temp table? Temp tables show up in tempdb but I am unable to do a right click and do a create script. Any help is appreciated.

@dotjoe 2012-04-24 19:09:01

@DotNetDude you can select ... into new_table to implicitly create an actual table.

@user423430 2017-11-15 22:10:18

Then grab rough column definition from empty table schema; replace '...' at the end with legit TABLE_NAME: declare @s varchar(max)='';select @[email protected]+','+COLUMN_NAME+' '+DATA_TYPE+isnull('('+case CHARACTER_MAXIMUM_LENGTH when -1 then 'max' else cast(CHARACTER_MAXIMUM_LENGTH as varchar(10))end+')','')from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='...';select @s

@David 2019-01-23 18:01:47

Cool hack! Thank you.

Related Questions

Sponsored Content

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

16 Answered Questions

[SOLVED] Select columns from result set of stored procedure

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

9 Answered Questions

[SOLVED] SQL Server Insert if not exist

24 Answered Questions

22 Answered Questions

[SOLVED] Check if table exists in SQL Server

37 Answered Questions

7 Answered Questions

[SOLVED] How to rename a table in SQL Server?

2 Answered Questions

Sponsored Content