Defining a SQL Server table from a stored procedure result set

Question

Does anyone know of a way to create a table definition from a stored procedure result set?

I have a stored procedure that creates a result set with 30 + columns, I would like to get this into a table without having to manually create all the columns of the table.

Is there a built-in procedure that will unload column names and types.?

Thank.

Answer 1

You can do this, but I don’t know if I recommend it:

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

SELECT
  *
INTO
  #table
FROM
  OPENROWSET(
    'SQLNCLI',
    'SERVER=.;Trusted_Connection=yes',
    'EXEC StoredProcedureName'
  )

Answer 2

If this is one time, you can try it (not in production!).

, , INTO YourNewTable FROM. 1 , .

, YourNewTable SQl Server Management Studio , :

SELECT 
    * 
    FROM INFORMATION_SCHEMA.Columns 
    WHERE TABLE_NAME='YourNewTable'
    ORDER BY ORDINAL_POSITION

Answer 3

"SELECT INTO" "WHERE 1 = 0", .

:

if you want a NOT NULL constraint, then you should use in select: isnull (yourcolumn, '123') as your column, and then in where where put WHERE yourcolumn <> '123'