locked
How to SELECT...INTO dynamically ? RRS feed

  • Question

  • I need to SELECT INTO destination dynamically, something like this:
    
    if @param = 0
       :setvar TARGETTABLE  T_FOO
    else
       :setvar TARGETTABLE  T_BAR
    
    SELECT.... INTO $(TARGETTABLE) FROM......;
    
    And looks like "$(TARGETTABLE)" always resolves to "T_BAR". How to do this properly ?
    Tuesday, December 27, 2011 7:50 PM

Answers

  • You can use dynamic SQL:

    http://www.sqlusa.com/bestpractices/dynamicsql/

    For conditional either IF...ELSE or CASE expression.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:09 PM
    Tuesday, December 27, 2011 7:52 PM
  • If you want to select into the permanent table (or global temp table), then you can use dynamic SQL here to construct the whole statement dynamically and execute it using execute sp_sqlexec. 
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by venku9 Wednesday, December 28, 2011 4:51 AM
    • Unproposed as answer by Kalman Toth Monday, January 2, 2012 12:31 PM
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:09 PM
    Tuesday, December 27, 2011 8:03 PM
  • Well, you could always use the same name in SELECT INTO, and then rename the table with sp_rename.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by vinaypugalia Wednesday, December 28, 2011 5:13 AM
    • Unproposed as answer by Kalman Toth Monday, January 2, 2012 12:32 PM
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:09 PM
    Tuesday, December 27, 2011 10:18 PM
  • Any progress?

    Alternative to dynamic SQL is to use SSIS.

    SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:09 PM
    Monday, January 2, 2012 12:33 PM
  • Try with the below sqlcode
    I need to SELECT INTO destination dynamically, something like this:
    
    DECLARE @TargetTableName as VARCHAR(30)

    DECLARE @StrQuery AS VARCHAR(MAX)

    SET @StrQuery=''

     if @param = 0
    BEGIN

     SET @TargetTableName ='Table1'

    END
    else
    BEGIN
     
      SET @TargetTableName ='Table2'

     END SET @StrQuery='SELECT.... INTO '+@TargetTableName+' FROM ....'

    EXEC(@StrQuery)

    PRINT @StrQuery

    PS.Shakeer Hussain
    • Proposed as answer by skc_chat Tuesday, January 3, 2012 11:37 AM
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:08 PM
    Monday, January 2, 2012 12:41 PM
  • WHY??  This proprietary  "feature" is against the SQL model. But is it just like the way we did this in the 1950's with magnetic tapes and could put a tape label on a scratch tape.  The main framer operator  would be asked to hand a new tape on a  tape drive and to give it a label (FILE NAME). 

    Oh, SQL programmers NEVER use an affix the like you "T_" to tell us that this is a table . In a correct data model (ISO-11179) the name of the table tells us what the entity or relationship  is, NEVER how it is PHYSICALLY modeled. 

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.  

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html 

     

     

     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:08 PM
    Monday, January 2, 2012 8:47 PM

All replies

  • You can use dynamic SQL:

    http://www.sqlusa.com/bestpractices/dynamicsql/

    For conditional either IF...ELSE or CASE expression.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:09 PM
    Tuesday, December 27, 2011 7:52 PM
  • If you want to select into the permanent table (or global temp table), then you can use dynamic SQL here to construct the whole statement dynamically and execute it using execute sp_sqlexec. 
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by venku9 Wednesday, December 28, 2011 4:51 AM
    • Unproposed as answer by Kalman Toth Monday, January 2, 2012 12:31 PM
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:09 PM
    Tuesday, December 27, 2011 8:03 PM
  • That does not work out, because you are mixing SQLCMD directives with T-SQL code. The SQLCMD directives are executed in SQLCMD and the T-SQL code in SQL Server which is a separate process.

    As pointed out in other posts, you could use dynamic SQL, but I get suspicious when I see the questions like this. What do you really want to achieve?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 27, 2011 8:33 PM
  • > What do you really want to achieve?

     

    Thanks Erland, I want to avoid having to have 2 SELECTs and if possible avoid turning *long* SQL into dynamic sql.

    Tuesday, December 27, 2011 9:12 PM
  • Well, you could always use the same name in SELECT INTO, and then rename the table with sp_rename.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by vinaypugalia Wednesday, December 28, 2011 5:13 AM
    • Unproposed as answer by Kalman Toth Monday, January 2, 2012 12:32 PM
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:09 PM
    Tuesday, December 27, 2011 10:18 PM
  • be careful using SELECT INTO, it can lock the sysobjects table on very large tables !!!
    Wednesday, December 28, 2011 3:20 AM
  • be careful using SELECT INTO, it can lock the sysobjects table on very large tables !!!


    Yes, in SQL 6.5 with page locking this can be really ugly. There can also be some problems in SQL 7, since the rows in sysobjects are lock during the entire statement. But starting with SQL 2000, the locks on the system tables are released once the table has been created.

    (And from SQL 2005, sysobjects is only a view.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 28, 2011 8:39 AM
  • Any progress?

    Alternative to dynamic SQL is to use SSIS.

    SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:09 PM
    Monday, January 2, 2012 12:33 PM
  • Try with the below sqlcode
    I need to SELECT INTO destination dynamically, something like this:
    
    DECLARE @TargetTableName as VARCHAR(30)

    DECLARE @StrQuery AS VARCHAR(MAX)

    SET @StrQuery=''

     if @param = 0
    BEGIN

     SET @TargetTableName ='Table1'

    END
    else
    BEGIN
     
      SET @TargetTableName ='Table2'

     END SET @StrQuery='SELECT.... INTO '+@TargetTableName+' FROM ....'

    EXEC(@StrQuery)

    PRINT @StrQuery

    PS.Shakeer Hussain
    • Proposed as answer by skc_chat Tuesday, January 3, 2012 11:37 AM
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:08 PM
    Monday, January 2, 2012 12:41 PM
  • WHY??  This proprietary  "feature" is against the SQL model. But is it just like the way we did this in the 1950's with magnetic tapes and could put a tape label on a scratch tape.  The main framer operator  would be asked to hand a new tape on a  tape drive and to give it a label (FILE NAME). 

    Oh, SQL programmers NEVER use an affix the like you "T_" to tell us that this is a table . In a correct data model (ISO-11179) the name of the table tells us what the entity or relationship  is, NEVER how it is PHYSICALLY modeled. 

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.  

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html 

     

     

     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    • Marked as answer by Kalman Toth Saturday, January 21, 2012 3:08 PM
    Monday, January 2, 2012 8:47 PM