CODEWITHSUNDEEP
sql-serverssms
PiotrWolkowski
PiotrWolkowski

Reputation: 8782

SSMS: Create a table script from results of a procedure

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

Upvotes: 1

Views: 296

Answers (3)

Dudi Konfino
Dudi Konfino

Reputation: 1136

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

enter image description here

Upvotes: 2

Dan
Dan

Reputation: 10680

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.

Upvotes: 2

Nick.Mc
Nick.Mc

Reputation: 19194

This will create a table called NewTable

SELECT *
INTO NewTable
FROM AnotherTable

Can you could alter your dynamic SQL to do this?

Upvotes: 0

Related Questions