Exporting Stored Procedure Results to a Table

  • pamelamooney1966

    SSC Veteran

    Points: 239

    Comments posted to this topic are about the item Exporting Stored Procedure Results to a Table

  • domenico.delbrocco

    Mr or Mrs. 500

    Points: 586

    which version of Management Studio are you using? I can't get this option, with Sql Server 2012

  • peter@hojbaktilsted.dk

    SSC Journeyman

    Points: 87

    I think it is either an option she has created herself, or from an add-in product like ssmstoolspack og ssmsboost, i cannot recall this ever to have been a part of the standard SSMS

    i use SSMSBoost, they have a similar option:

    • right click in results tab
    • select 'Script Grid Data'
    • you get several templates to choose from, on of them 'CREATE TABLE'

    regards Peter

  • pamelamooney1966

    SSC Veteran

    Points: 239

    I am using SQL Server 2016, but I  do have the SSMS Tools Pack. I should have mentioned that in the post. Sorry for any confusion!

  • tskelley

    SSCommitted

    Points: 1868

    Ran into the same issue, but I have SSMS Tools Pack and do not have the 'Script as INSERT' option.  I do have the save as Excel and script or search grid results, but this will not give me the table.  I am using ver. 4.9.6, and noticed they have a 5.0.0 release.  Do you think this is another tool, as I did not notice the Excel or search options in your context menu?

     

  • tskelley

    SSCommitted

    Points: 1868

    Never you mind, found it, it's a SQL Prompt by Redgate feature...

  • tripleAxe

    SSCertifiable

    Points: 5522

    You can also use the Import/Export wizard to generate the table structure if you don't have the SSMS tools pack or SQL prompt installed.

     

  • srienstr

    SSCrazy

    Points: 2244

    You can also potentially pull the table information  from tempdb.information_schema.columns and build the CREATE script from that. More steps, but reasonably quick after you've done it a time or two.


    Puto me cogitare, ergo puto me esse.
    I think that I think, therefore I think that I am.

  • rick.foster

    Old Hand

    Points: 360

    Hi,

    There is a small mistake in your code, as written the code will run forever as the loop variable is not incremented.  You need a Begin Statement after the While and End statement after the  Set @i....

    DECLARE @i INT = 0;

    WHILE @i < 200

    INSERT INTO #RowCountTablePrelim

    SELECT '[' + DB_NAME() + N']' + '.' + '[' + SCHEMA_NAME(schema_id) + ']' + '.' + '[' + t.name + ']' AS TableName,

    SUM(ps.row_count) AS [RowCount]

    FROM sys.tables AS t

    INNER JOIN sys.dm_db_partition_stats AS ps

    ON t.object_id = ps.object_id

    AND ps.index_id < 2

    AND ps.row_count > 5000

    GROUP BY t.schema_id,

    t.name,

    ps.row_count;

    SET @i = @i + 1;

     

  • pamelamooney1966

    SSC Veteran

    Points: 239

    Hi Rick,

    I had actually forgotten the BEGIN and END statements to the loop, which as you pointed out, would make it run indefinitely (I had incremented the loop as you suggested - please review code).  Please see revised code below.  It will not only work, but will also count the loop for you in the Messages tab.  Thank you for bringing it to my attention!

    SET NOCOUNT ON;
    IF OBJECT_ID('tempdb..#IndexInfo') IS NOT NULL
    DROP TABLE #IndexInfo;
    IF OBJECT_ID('tempdb..#HoldingTable') IS NOT NULL
    DROP TABLE #HoldingTable;
    IF OBJECT_ID('tempdb..#RowCountTablePrelim') IS NOT NULL
    DROP TABLE #RowCountTablePrelim;
    IF OBJECT_ID('tempdb..#RowCountTableFinal') IS NOT NULL
    DROP TABLE #RowCountTableFinal;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    CREATE TABLE #RowCountTablePrelim
    (
    TableName sysname NULL,
    [RowCount] BIGINT NULL
    );
    CREATE TABLE #RowCountTableFinal
    (
    TableName sysname NULL,
    [RowCount] BIGINT NULL
    );

    USE AdventureWorks2012;
    GO
    DECLARE @i INT = 0;

    WHILE @i < 200

    BEGIN

    PRINT @i;

    INSERT INTO #RowCountTablePrelim

    SELECT '[' + DB_NAME() + N']' + '.' + '[' + SCHEMA_NAME(schema_id) + ']' + '.' + '[' + t.name + ']' AS TableName,

    SUM(ps.row_count) AS [RowCount]

    FROM sys.tables AS t

    INNER JOIN sys.dm_db_partition_stats AS ps

    ON t.object_id = ps.object_id

    AND ps.index_id < 2

    AND ps.row_count > 5000

    GROUP BY t.schema_id,

    t.name,

    ps.row_count;

    SET @i = @i + 1;

    END

    INSERT INTO #RowCountTableFinal
    (
    TableName,
    [RowCount]
    )
    SELECT TableName,
    SUM([RowCount])
    FROM #RowCountTablePrelim
    GROUP BY ROLLUP(TableName);
    SELECT *
    FROM #RowCountTablePrelim
    ORDER BY [RowCount] DESC;
    DROP TABLE #RowCountTableFinal;
    DROP TABLE #RowCountTablePrelim;

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply