Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert result of Stored Procedure into Temp Table without declaring Temp Table Columns

Tags:

sql

sql-server

I want to insert the values of stored procedure into a temp table without predefining the columns for the temp table.

Insert Into #Temp1 Exec dbo.sp_GetAllData @Name = 'Jason'.

How can I do this ? I saw an option as below but can I do it without mentioning the server name ?

SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC tempdb.dbo.GetDBNames')
-- Select Table
SELECT *
FROM #TestTableT;
like image 598
Jhjry smth Avatar asked Mar 23 '17 18:03

Jhjry smth


People also ask

How to insert the stored procedure result into temporary table?

In this SQL Server example, we will create a Temporary Table. Next, we are going to use the INSERT INTO SELECT Statement to insert the Stored Procedure result into Temporary Table In this Frequently Asked Question, we use the SELECT INTO Statement, and OPENROWSET to insert the Stored Procedure result into Temporary Table

What is a temp procedure in SQL?

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. CREATE TABLE #temp (...);

Can we insert the result of a stored procedure without pre-creation?

Answer: Very simple question, but the answer is sometimes not as easy as we want. Here is a simple script which demonstrates how we can insert the result of the stored procedure into a temporary table without pre-creating the temporary table before the stored procedure is executed.

How to insert stored procedure output into studentdata_log?

CREATE TABLE #StudentData_Log (ID INT, Name VARCHAR (100)) SELECT * FROM #StudentData_Log; Lets execute the stored procedure and insert output into above temp table. INSERT INTO #StudentData_Log EXEC dbo.FetchStudentData GO Lets check the temp table, and you can see the stored procedure output is inserted in table. Also Read..


2 Answers

I could not find a possible solution without defining temp table schema and writing server name. So, I changed the code and the queries to handle with only known schema. Code example is as below

    CREATE TABLE #TestTable ([name] NVARCHAR(256), [database_ID] INT);
    INSERT INTO #TestTable
    EXEC GetDBNames

    SELECT * FROM #TestTable;

As provided in the link https://blog.sqlauthority.com/2013/05/27/sql-server-how-to-insert-data-from-stored-procedure-to-table-2-different-methods/

like image 145
Jhjry smth Avatar answered Oct 26 '22 09:10

Jhjry smth


No-one said it had to be pretty:

CREATE PROCEDURE p AS
SELECT 1 as x, 2 as y, 3 as z

DECLARE c CURSOR FOR
SELECT 
name, system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('p'), 0);
DECLARE @name sysname, @type sysname;

CREATE TABLE #t(fake int)

OPEN c
FETCH NEXT from c into @name, @type
WHILE (@@FETCH_STATUS = 0)
BEGIN

 EXEC ('ALTER TABLE #t ADD ' + @name + ' ' + @type)
 FETCH NEXT from c into @name, @type
END

CLOSE C
DEALLOCATE c
ALTER TABLE #t DROP COLUMN fake;

INSERT INTO #t EXEC p;
like image 35
Gavin Campbell Avatar answered Oct 26 '22 07:10

Gavin Campbell