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 stackoverflow.com/questions/8805275/… — Giorgos Betsos
– Giorgos Betsos, Commented Feb 12, 2015 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
– PiotrWolkowski, Commented Feb 12, 2015 at 13:47
Perhaps you can alter your dynamic sql query to do the insert into the temp table? — jpw
– jpw, Commented Feb 12, 2015 at 13:50
@jpw Is it possible without manually declaring the temp table? I wont to avoid typing in all +100 columns. — PiotrWolkowski
– PiotrWolkowski, Commented Feb 12, 2015 at 13:53
@jpw I've added a mocked version of the procedure in question. — PiotrWolkowski
– PiotrWolkowski, Commented Feb 12, 2015 at 14:36

Community · Accepted Answer · 2017-05-23 10:26:44Z

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, 2017 at 10:26

CommunityBot

11 silver badge

answered Feb 12, 2015 at 14:06

Dan

10.8k27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PiotrWolkowski Over a year ago

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 · Accepted Answer · 2015-02-14 19:13:18Z

2

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

enter image description here

edited Feb 14, 2015 at 19:13

PiotrWolkowski

8,8007 gold badges53 silver badges78 bronze badges

answered Feb 12, 2015 at 19:53

Dudi Konfino

1,1242 gold badges13 silver badges24 bronze badges

Comments

Nick.Mc · Accepted Answer · 2015-02-12 14:05:05Z

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, 2015 at 14:05

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Collectives™ on Stack Overflow

SSMS: Create a table script from results of a procedure

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related