How to insert result of stored procedure into temp table without declaring topic table columns
I want to insert values of a stored procedure into a temporary table without pre-defining columns for the temp table.
Insert Into #Temp1 Exec dbo.sp_GetAllData @Name = 'Jason'.
How can i do this? I've seen an option like 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;
+3
source to share
2 answers
I couldn't find a possible solution without defining the temp table schema and recording the server name. So, I changed the code and queries to only handle the known schema. Sample code is given below
CREATE TABLE #TestTable ([name] NVARCHAR(256), [database_ID] INT);
INSERT INTO #TestTable
EXEC GetDBNames
SELECT * FROM #TestTable;
As stated in the link https://blog.sqlauthority.com/2013/05/27/sql-server-how-to-insert-data-from-stored-procedure-to-table-2-different-methods/
+2
source to share
Nobody said that it should be beautiful:
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;
0
source to share