0

I need to define a table name as a variable and use that variable in a query.

I need to DECLARE @variable VARCHAR(30) = 'Paris' and then thanks to a SELECT * INTO create a table named tbl_Paris

something like SELECT * INTO + tbl_@variable

Is there a way to do that?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113
  • A table's type is `table`. Eg `declare @myTable table (id int, name nvarchar(20)…)`. After that, you treat it just like any other table. BTW that's explained in the docs – Panagiotis Kanavos Oct 11 '18 at 10:14
  • 1
    No, you must use dynamic SQL. See [this article](http://www.sommarskog.se/dynamic_sql.html). – Dan Guzman Oct 11 '18 at 10:17
  • 1
    Are you asking how to pass the table name as a parameter? The title says something completely different - how to define `a table as a @variable`. It's not SSMS that's funky. And *tables* aren't parameters. How a query executes depends on the table schema, indexes and table *data*. It's as fundamental as a concrete type in other languages – Panagiotis Kanavos Oct 11 '18 at 10:23
  • Why do you want to "parameterize" the table name in the first place? There are probably other, faster and safer ways to do whatever you want – Panagiotis Kanavos Oct 11 '18 at 10:24
  • @PanagiotisKanavos is correct, you need to research table variables. However, depending on the size of the data and performance required, a temporary table may be better suited. – Apep Oct 11 '18 at 10:25
  • The question is clearly asking about using the table NAME as a parameter, not using a table VARIABLE. – MJH Oct 11 '18 at 10:26
  • @Apep they are no different in terms of performance. Both are stored in tempdb – Panagiotis Kanavos Oct 11 '18 at 10:28
  • @MJH that's clear only if you don't read the title. – Panagiotis Kanavos Oct 11 '18 at 10:28
  • I need to "parameterize" the table because I then want to use @MyVariable to create tbl_MYVariable, so each table has got the name of the variable I have used – Francesco Mantovani Oct 11 '18 at 10:29
  • @Panagiotis Kanavos Reading the first couple of lines of the question makes it clear what the OP is asking. – MJH Oct 11 '18 at 10:30
  • @FrancescoMantovani you should explain what the *actual* problem is then. You want to generate `CREATE TABLE` statements based on query results or a list of values. – Panagiotis Kanavos Oct 11 '18 at 10:31
  • @MJH I did. The very first, very big, very bold line that says table variable. It's the title. People that thought this is about table variables are perfectly justified. – Panagiotis Kanavos Oct 11 '18 at 10:32
  • @FrancescoMantovani depending on what you want to do you can find scripts that generate tables, columns, constraints etc. If you want to "clone" a table you could check the `sys.columns` table to find column names, types, sizes, nullability. – Panagiotis Kanavos Oct 11 '18 at 10:35
  • Please, don't fight, guys. I need to `DECLARE @variable VARCHAR(30) = 'Paris'` and then thanks to a `SELECT * INTO` create a table named tbl_Paris – Francesco Mantovani Oct 11 '18 at 10:36
  • sorry, @PanagiotisKanavos , I edited my question. I hope is more clear now. Thank you again – Francesco Mantovani Oct 11 '18 at 10:41
  • @PanagiotisKanavos There is absolutely a difference in performance between table variables and temporary tables. I've recently been investigating this myself after adopting someone's code that heavily uses table variables. If you're not using OPTION(RECOMPILE) or TRACE FLAG 2453 (https://www.brentozar.com/archive/2017/02/using-trace-flag-2453-improve-table-variable-performance/) you'll definitely notice a difference, particularly when it comes to larger data sets. – Apep Oct 11 '18 at 10:50
  • Hi @PanagiotisKanavos, I edited my question as it was misleading. I think it cannot be labelled as a duplicate. My goal is to give to a table the name of a variable and the article you linked doesn't do that. Let me know what you think. Thank you – Francesco Mantovani Oct 11 '18 at 20:04

1 Answers1

1

Try this.

DECLARE @TableName VARCHAR(20) = 'your_table_name'
DECLARE @sqlCommand VARCHAR(1000)
SET @sqlCommand = 'SELECT * from tbl_' + @TableName
EXEC (@sqlCommand)
AswinRajaram
  • 1,519
  • 7
  • 18