locked
Store output of SQL Server EXEC into a table RRS feed

  • Question

  • Experts,

    I wan't to see below code, how can I use it:-

    EXEC (@SQL)
    INTO Table-Name

    I want to store o/p of EXEC SP into below Table-Name which I also wan't to create first on fly. Any Help please?


    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Monday, August 15, 2011 4:22 PM

Answers

  • As I said, the only solution is to embed INTO Permanent table into dynamic SQL as I showed.

    The alternative solution is to create the table (could be temp table) before calling the @SQL.

    Then there could be two variations:

     

    INSERT INTO #MyCreatedTable -- also can use table variable
    
    EXEC (@SQL)
    
    
    

    or

     

    create table #SomeTempTable ....
    
    set @SQL = 'INSERT INTO #SomeTempTable ...
    
    dynamic SQL here...'
    
    execute (@SQL)
    
    
    

    The later variation allows to avoid the 'INSERT EXEC can no be nested' error.

    Also, may be I was not clear. The permanent table should not exists when you call your code, it will be created with INTO, e.g.

     

     

    USE myDB
    
    IF OBJECT_ID('MyResultTable','U') IS NOT NULL DROP TABLE MyResultTable
    
    
    
    set @SQL = 'SELECT ... INTO MyResultTable ...'
    
    execute (@SQL)

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by gk1393 Monday, August 15, 2011 8:37 PM
    Monday, August 15, 2011 5:31 PM

All replies

  • You can have

     

    set @SQL = 'SELECT ...
    
    INTO PermanentTable
    
    ....'
    
    execute (@SQL)
    

     

     

    You will be able to run this query and create a permanent table.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Kent Waldrop Monday, August 15, 2011 4:28 PM
    • Unproposed as answer by gk1393 Monday, August 15, 2011 5:14 PM
    Monday, August 15, 2011 4:24 PM
  • Hallo Kumar,

    as the experts have answered your question I would recommend to avoid EXEC and use sp_executeSQL instead of.

    Have a look to a pretty fine article from Erland who gives pretty good examples for dynamic sql and why not to use...

    http://www.sommarskog.se/dynamic_sql.html

     


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Monday, August 15, 2011 4:28 PM
  • Hi Naomi,

    Currently we don't have any permanent Table, dynamic sql creates number of columns and everything is working perfectly as it displays the result -set under SSMS if you use only

    EXEC (@SQL).

    But now we want to store this result which is procedured by the above into permanent query AND IMP THING IS PERMANENT this is permanent doesn't exist currently into our DB we want to use INTO clause instead of INSERT INTO clause.

    Something like this:-

    EXEC (SQL)
    INTO Table-Name

    I hope that sense. if not please do let me know.

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Monday, August 15, 2011 5:26 PM
  • As I said, the only solution is to embed INTO Permanent table into dynamic SQL as I showed.

    The alternative solution is to create the table (could be temp table) before calling the @SQL.

    Then there could be two variations:

     

    INSERT INTO #MyCreatedTable -- also can use table variable
    
    EXEC (@SQL)
    
    
    

    or

     

    create table #SomeTempTable ....
    
    set @SQL = 'INSERT INTO #SomeTempTable ...
    
    dynamic SQL here...'
    
    execute (@SQL)
    
    
    

    The later variation allows to avoid the 'INSERT EXEC can no be nested' error.

    Also, may be I was not clear. The permanent table should not exists when you call your code, it will be created with INTO, e.g.

     

     

    USE myDB
    
    IF OBJECT_ID('MyResultTable','U') IS NOT NULL DROP TABLE MyResultTable
    
    
    
    set @SQL = 'SELECT ... INTO MyResultTable ...'
    
    execute (@SQL)

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by gk1393 Monday, August 15, 2011 8:37 PM
    Monday, August 15, 2011 5:31 PM
  • Make sense and makes sense. If you dream up your own syntax, http://connect.microsoft.com/SqlServer/Feedback/
    is the place to go if you want it implemented.

    There are actually hacks to do this, but they are bad practice and I am not going to show it. The best is to define your table in advance. There is also the alternative to use a global temp table.

    An important observation is that when you run into situations like this where the tool does not seem to have the feature you want, you have probably deviated from the norm on an earlier stage.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, August 15, 2011 9:11 PM