sql with Insert results of a stored procedure into a temporary table
stored procedure to insert data into table in sql server (20)
How do I do a
SELECT * INTO [temp table] FROM [stored procedure]? Not
FROM [Table] and without defining
Select all data from
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'
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.
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 ) RETURNS TABLE AS RETURN SELECT * FROM customers WHERE RegionID = @RegionID GO
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 ) AS BEGIN SELECT * FROM CustomersbyRegion(@regionID); END GO
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.
If you're lucky enough to have SQL 2012 or higher, you can use
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 + ' (' begin select @sql = @sql + '[' + r.name + '] ' + 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 p.name = @procname set @sql = substring(@sql,1,len(@sql)-1) + ')' execute (@sql) execute('insert ##' + @procname + ' exec ' + @procname) end
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.
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.
DECLARE @t TABLE ( --these columns were copied from sp_help COL1 INT, COL2 INT ) INSERT INTO @t 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.
If the query doesn't contain parameter, use
OpenQuery else use
Basic thing would be to create schema as per stored procedure and insert into that table. e.g.:
DECLARE @abc TABLE( 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 ) INSERT INTO @abc EXEC [dbo].[usp_MySp] 726,3 SELECT * FROM @abc
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
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, http://www.sommarskog.se/share_data.html#OPENQUERY
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.
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.
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 ) AS SET @SQL = 'CREATE TABLE ' + @tableName + ' (' SELECT @SQL = @SQL + '['+name +'] '+ system_type_name +'' + ',' FROM sys.dm_exec_describe_first_result_set_for_object ( OBJECT_ID(@ProcedureName), NULL ); --Remove trailing comma SET @SQL = SUBSTRING(@SQL,0,LEN(@SQL)) SET @SQL = @SQL +')'
To use the procedure, call it in the following way:
DECLARE @SQL NVARCHAR(MAX) exec dbo.usp_GetStoredProcTableDefinition @ProcedureName='dbo.usp_YourProcedure', @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.
CREATE TABLE #temp (...); INSERT INTO #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.
SELECT * INTO #temp FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC [db].[schema].[sproc]')
I would do the following
Create (convert SP to) a UDF (Table value UDF).
select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'
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))
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 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
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:
CREATE TABLE #tmpBus ( COL1 INT, COL2 INT ) INSERT INTO #tmpBus Exec SpGetRecords 'Params'
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;
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):
GO create procedure #TempTableForSP(@tableId int, @procedureId int) as begin 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(c.name) + '';''+ 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; end GO 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).
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.
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)
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 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.
- I'm creating a table with the following schema and data.
- Create a stored procedure.
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, CONSTRAINT [PK_tblTestingTree] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO 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 as begin select Id,ParentId from tblTestingTree end create table tbltemp ( id int, ParentId int ) insert into tbltemp exec GetDate select * from tbltemp;
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' DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName ' SELECT @strSQL = @strSQL+STUFF(( SELECT ',' +name+' ' + system_type_name FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storeProcname),0) FOR XML PATH('') ),1,1,'(') + ')' EXEC (@strSQL) INSERT INTO myTableName 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.
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.