locked
Use dynamic sql to 'insert into @table' RRS feed

  • Question

  • I have a sql that needs to be dynamic. The database name changes and so do the conditions.

    Have the following value that I do a REPLACE for the variables inside the question marks and then use in a EXECUTE sp_executesql.

    But I am having trouble with the ?TableName? because it is a declared table - @tablename.

    How can i get this insert statement to work with my @tablename? Thanks.

    'INSERT INTO ?TableName? SELECT name FROM ?DBName?.sys.tables WHERE [Type] = ''U'' ?Condition2? ?Condition3? ORDER BY name'

    • Edited by TheBrenda Friday, September 13, 2013 6:47 PM
    Friday, September 13, 2013 6:41 PM

Answers

  • Use Temporary Table instead of Table Variable.

    more info:

    Table Variable and Dynamic SQL


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
    Saeid Hasani's home page

    • Proposed as answer by Naomi N Monday, September 16, 2013 12:51 AM
    • Marked as answer by Allen Li - MSFT Sunday, September 22, 2013 1:15 PM
    Friday, September 13, 2013 7:05 PM

All replies

  • Use Temporary Table instead of Table Variable.

    more info:

    Table Variable and Dynamic SQL


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
    Saeid Hasani's home page

    • Proposed as answer by Naomi N Monday, September 16, 2013 12:51 AM
    • Marked as answer by Allen Li - MSFT Sunday, September 22, 2013 1:15 PM
    Friday, September 13, 2013 7:05 PM
  • Realized that the INSERT SELECT FROM would work with the INSERT coded and only the SELECT FROM using dynamic SQL.

    INSERT INTO @tablename
     EXECUTE sp_executesql @SQLUPDATE

    Friday, September 13, 2013 7:27 PM
  • >> I have an SQL that needs to be dynamic. The database name changes and so do the conditions. <<

    Then it is a different statement! in a different data model! This is sometimes called “Automobiles, Squids and Lady Gaga” programming. Dynamic SQL is how you tell the world you have no design, no data model, no idea how to use RDBMS. An unknown random user can make better decisions than you can snd you admit it!


    --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

    • Proposed as answer by Dean Savović Saturday, September 14, 2013 7:58 PM
    Friday, September 13, 2013 10:43 PM
  • Actually ... “Automobiles, Squids and Lady Gaga” is only used by you and not just sometimes. It seems to be your snarky signature. Guess you are hoping that if you use it enough it might catch on and it would be your claim to SQL fame? Sounds like George in an episode of Seinfeld. I miss that show. 
    Saturday, September 14, 2013 5:37 PM
  • The original phrase was “Automobiles, Squids and Britney Spears” in a news blog. She had just shaved her head and someone had landed a giant squid. The poster asked who was uglier and made comments about incoherent, unrelated news stories of the day. I picked it up as a cute tag line for low cohesion coding of any kind. 

    Later, Phil Factor at Red Gate's SQL site picked the phrase up and edited it to “Lady Gaga” for the younger readers. It probably needs to be “Miley Cyrus” today. 

    --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

    Saturday, September 14, 2013 7:54 PM
  • You are right. It works but it's not a good practice.

    Your problem is how to share data between stored procedures. And the best way for your situation is sharing a temporary table. Please have a look at this great article from Erland:

    Sharing a Temp Table


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
    Saeid Hasani's home page


    Sunday, September 15, 2013 6:29 AM
  • So ... instead of hitting your preprogrammed insult key, can you take a couple of minutes and point me in the right direction. I am very trainable. 

    I need to write an INSERT INTO SELECT FROM where the FROM database name is unknown until execution time. We have a database template that is distributed to each customer and they can call the database whatever they want. Some servers host multiple databases so we cannot enforce a database name. They are usually called something like Store111, Store123, etc.

    Without knowing the database name, how can I write without using dynamic SQL? I can code the ?Condition1? and 2, and I can make the ?TableName? a permanent table. But the ?DBName? I do not know how to get around without using dynamic SQL.

    INSERT INTO ?TableName? SELECT name FROM ?DBName?.sys.tables WHERE [Type] = ''U'' ?Condition2? ?Condition3? ORDER BY name'

    Thanks

    Sunday, September 15, 2013 7:44 PM
  • The onteresting question is: What are you actually doing here? Is this a kind of deployment process or do you need these statements for normal use  in your database/application?

    Use string concatenation to build your statement. When it's for production use, create a stored procedure from this.

    But it really depends on your concrete needs.

    Sunday, September 15, 2013 8:37 PM
  • Generally speaking, one works within a given database by "using" that database when the connection is established.  Therefore, you don't need to specify the database since the current one is implied when an object name that is not database-qualified is encountered.  In addition, you are selecting from sys.tables - so the next obvous question is why?  That would be an unusual thing to do for application-level functionality. 

    You may also have developed some bad habits - an insert statement should generally specify the columns that will be populated in the inserted table (but perhaps that was just a discussion shortcut?).  Misuse or misunderstanding of table variables vs. temp tables is another common problem.

    Lastly, you are using rather odd notations in your tsql statements (e.g., "?Condition2?a ...") - the implication is that these statements are generated in the application and executed dynamically by the application.  Ignoring the security issues (which are significant), then it seems to me that there is no need to qualify your object names with the database since you can change the current database to the desired one at any time - which of course leads me back to the first comment regarding "using" the database. 

    It sounds like you are in need of some expertise in designing your system (or perhaps just in using what already exists).  Unfortunately, that type of advice is difficult to give from a distance - and you should be wary of accepting anything read on the internet.  I also suggest you take some time in composing your questions.  You have a habit of asking very generic questions without supplying much useful detail - including the version of sql server that you are using.  This thread is an example of that - you started with a simple question about dynamic sql using a table variable and it has evolved into an architecture question. Allow me to suggest that when you find yourself faced with what seems like an insurmountable problem in tsql (and assuming you have a good understanding of sql), the best solution is likely to be found in either a change in your schema or in an change in your approach to solving your problem. 

    Monday, September 16, 2013 2:32 PM