7

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;
Jhjry smth
  • 197
  • 1
  • 1
  • 12
  • We are using multiple stored procedures and the number of columns output may change. Writing a server name might not be a good way from maintenance stand point – Jhjry smth Mar 23 '17 at 18:54
  • Sounds like there are some design issues here. You are creating the one proc to rule them all. This is like making a single method in a dotnet class that can do everything. – Sean Lange Mar 23 '17 at 18:57
  • 1
    Then why don't you just use a Table-Valued function instead of an Stored Procedure. You will be able to directly select the result. – Marc Guillot Mar 23 '17 at 18:58
  • You can create a temporary table with one fake column and alter this table in a stored procedure – Mikhail Lobanov Mar 24 '17 at 15:41
  • Possible duplicate of [Insert results of a stored procedure into a temporary table](https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) – underscore_d Apr 19 '18 at 12:29

2 Answers2

2

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/

Jhjry smth
  • 197
  • 1
  • 1
  • 12
2

No-one said it had to be pretty:

CREATE PROCEDURE p AS
SELECT 1 as x, 2 as y, 3 as z
GO
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;
GO
Dan Nolan
  • 4,733
  • 2
  • 25
  • 27
Gavin Campbell
  • 796
  • 5
  • 19