SSMS: Create a table script from results of a procedure

Question

I have a piece of dynamic sql query. I'd like to keep the result in a temp table. But the query returns +100 fields so I'd prefer not to type the Create table script manually.

Is it possible in Sql Server Management Studio to script out the results returned by the procedure into a create table script.

So let's say that in a new query panel I execute my script. Below I get the returned results. Option I'm looking for is something like e.g. right clicking the results and selecting "Script into a table". Or something similar to what you get when you select "Edit top 200 rows" on a table and then in the returned results when you right click -> Pane -> SQL you can edit the SQL that generated the results.

Is anything like that possible in SSMS for results of a procedure? If external tools are required are there any that are free? I'm using SQL Server 2012 and SSMS 2012. Some tools that where available for previous versions are licensed for 2012.

Below some mocked version of the core code in the procedure:

exec dbo.spDynamic
    @ID , 
    @ID2 ,
    @Parameters ,
    @prefixLogic,
    @selectprefix,
    @selectsuffix,
    @Table_1,
    @Function_1,
    @SelectExtra_1,
    @Where_1,
    @Function_2,
    @SelectExtra_2,
    @Where_2,
    @On,
    @finalWhere
    @tempSchema
    @tempTable
    @tempWhere

And here's the essential part of the spDynamic:

Declare @sql nvarchar(max)
set @sql =
    + @parameterLogic 
    + ' ' + @prefixlogic
    + @selectprefix
    + ' SELECT ' + @DeltaLogic
    + ' FROM ( Select * ' + Case When @ID < 0 Then Replace(@SelectExtra_1, '<ID>', CAST(@ID AS nvarchar))
                                When @ID = 0 Then Replace(@SelectExtra_2, '<ID>', CAST(@ID AS nvarchar))
                                Else Replace(@Table_1, '<ID>', CAST(@ID AS nvarchar)) End

        + ' From ' + Case When @ID < 0 Then @Function_1 + ' ' + @Where_1 
                        When @ID = 0 Then @Function_2 + ' ' + @Where_2 
                        Else @tempSchema + @tempTable 
        + ' ' + Replace(@tempWhere, '<ID>', CAST(@ID AS nvarchar)) End
    + ' ) A '  
    + ' FULL OUTER JOIN '
    + ' ( Select * ' + Case When @ID2 < 0 Then Replace(@SelectExtra_1, '<ID>', CAST(@ID2 AS nvarchar))
                                When @ID2 = 0 Then Replace(@SelectExtra_2, '<ID>', CAST(@ID2 AS nvarchar))
                                Else Replace(@Table_1, '<ID>', CAST(@ID2 AS nvarchar)) End
        + ' From ' + Case When @ID2 < 0 Then @Function_1 + ' ' + @Where_1 
                When @ID2 = 0 Then @Function_2 + ' ' + @Where_2 
                Else @tempSchema + @tempTable 
        + ' ' + Replace(@tempWhere, '<ID>', CAST(@ID2 AS nvarchar)) End
    + ' ) B ' + @on
    + @finalWhere
    + @selectsuffix

exec sp_sqlexec @sql

You may find this helpful http://stackoverflow.com/questions/8805275/create-an-insert-script-from-select-results — Giorgos Betsos, Feb 12 '15 at 13:34
Yes, I had been checking that one before I asked my question. Unfortunately I have to rely on free tools if possible. I'll add that to the question. As to other suggestions I'd prefer avoiding typing the table columns manually. — PiotrWolkowski, Feb 12 '15 at 13:47
Perhaps you can alter your dynamic sql query to do the insert into the temp table? — jpw, Feb 12 '15 at 13:50
@jpw Is it possible without manually declaring the temp table? I wont to avoid typing in all +100 columns. — PiotrWolkowski, Feb 12 '15 at 13:53
@jpw I've added a mocked version of the procedure in question. — PiotrWolkowski, Feb 12 '15 at 14:36

score 2 · Accepted Answer · edited May 23 '17 at 10:26

2

One solution could be to execute the stored procedure inside an OPENROWSET function as described here.

This allows you to create the table on-the-fly, for example by writing:

SELECT * INTO MyTempTable
FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes',
    'EXEC myStoredProc')

If you don't want actual data into MyTempTable, you could add WHERE 1 = 0 at the end. Afterwards, you can then script out MyTempTable just like any other table.

edited May 23 '17 at 10:26

Community

1
1

answered Feb 12 '15 at 14:06

Dan

10,480
23
49

I keep getting some errors. I guess there are some errors in the connections string. I'm also getting “Deferred prepare could not be completed". Still `OPENROWSET` sounds like right way to proceed. – PiotrWolkowski Feb 12 '15 at 16:11

score 2 · Answer 2 · edited Feb 14 '15 at 19:13

2

If you use SSMSBoost tool bar then it has a "script grid data" option:

enter image description here

edited Feb 14 '15 at 19:13

PiotrWolkowski

8,408
6
48
68

answered Feb 12 '15 at 19:53

Dudi Konfino

1,126
2
13
24

score 0 · Answer 3 · answered Feb 12 '15 at 14:05

0

This will create a table called NewTable

SELECT *
INTO NewTable
FROM AnotherTable

Can you could alter your dynamic SQL to do this?

answered Feb 12 '15 at 14:05

Nick.Mc

18,304
6
61
91

SSMS: Create a table script from results of a procedure

3 Answers3