Insert results of a stored procedure into a temporary table

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

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.

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:

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

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,
--

EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE

----------------------------------------------------------------

Code:
--

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
FROM OPENQUERY(DBASESERVER,
'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**,

[To see links please register here]

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.

**Code**

CREATE TABLE #T1
(
col1 INT NOT NULL,
col2 NCHAR(50) NOT NULL,
col3 TEXT NOT NULL,
col4 DATETIME NULL,
col5 NCHAR(50) NULL,
col6 CHAR(2) NULL,
col6 NCHAR(100) NULL,
col7 INT NULL,
col8 NCHAR(50) NULL,
col9 DATETIME NULL,
col10 DATETIME 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'127.0.0.1'
SET @Para5 = N'XXXXXXX'
SET @Para6 = N'X'
SET @Para7 = N'X'

INSERT INTO #T1
(
col1,
col2,
col3,
col4,
col5,
col6,
col6,
col7,
col8,
col9,
col10,
)
EXEC [dbo].[usp_ProcedureName] @Para1, @Para2, @Para3, @Para4, @Para5, @Para6, @Para6

I hope this helps. Please qualify as appropriate.

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;

You can use [OPENROWSET][1] 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
AS
BEGIN
SELECT * FROM sys.databases
END
GO

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

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

SELECT * FROM #MyTempTable


[1]:

[To see links please register here]

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][1] (for SQL Server 2000, in fact):

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales

[1]:

[To see links please register here]

This stored proc does the job:

CREATE PROCEDURE [dbo].[ExecIntoTable]
(
@tableName NVARCHAR(256),
@storedProcWithParameters NVARCHAR(MAX)
)
AS
BEGIN
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)), '') +
';trusted_connection=yes'''

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

SET @sql = '
SELECT
*
INTO
' + @tableName + '
FROM
OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'

EXEC (@sql)
END
GO


It's a slight rework of this: [Insert stored procedure results into table][1] 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.


[1]:

[To see links please register here]

I found *[Passing Arrays/DataTables into Stored Procedures][1]* 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.

[1]:

[To see links please register here]