Use of variable table name in Query

Question

From time to time I need to run the same SQL Server script, every time changing one or two letters in each table name to a value which is different each time I run the script.

How can I set once at the beginning of the script, so that I do not need to change each table name?

Here is a simplified example of the script, which in reality contains a lot more tables:

SELECT * FROM T_Atable1 
    WHERE ref IN (SELECT ref FROM T_Atable2)
         AND customer IN (SELECT customer FROM T_Atable3)

Table names are of the form T_ where:

"T_" is fixed
<id> is the variable part of the table name. In the above example = "A"
<table name> is the fixed name of the table. In the above example, the fixed parts are "table1", "table2", "table3"

I suspect I need to use dynamic SQL but do not know how to do this.

SQL Server could be any version 2003+, depending on the system containing the data.

Does this answer your question? A table name as a variable

GSerg
– GSerg

2022-11-11 00:36:33 +00:00
Commented Nov 11, 2022 at 0:36 — GSerg
– GSerg, Commented Nov 11, 2022 at 0:36

Allan S. Hansen · Accepted Answer · 2014-07-03 05:46:32Z

3

You'll need to use Dyanmic SQL. Dynamic SQL is simply that you build your query "dynamically" in a string either in your SQL procedure or in an application, and then you execute that string.

For example;

DECLARE @tableVar1 VARCHAR(255) = 'T_Atable1'
DECLARE @tableVar2 VARCHAR(255) = 'T_Atable2'
DECLARE @tableVar3 VARCHAR(255) = 'T_Atable3'


EXEC('
    SELECT * FROM ' + @tableVar1 + '
    WHERE ref IN (SELECT ref FROM ' + @tableVar2 + ')
    AND customer IN (SELECT customer FROM ' + @tableVar3 + ')
')

answered Jul 3, 2014 at 5:46

Allan S. Hansen

4,09126 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andomar Over a year ago

I don't think exec allows you to concatenate an SQL string in-place. ecec('select' + ' 1') gives an error.

Allan S. Hansen Over a year ago

@Andomar : It does; DECLARE @tableVar1 VARCHAR(255) = 'A'; EXEC('SELECT * FROM ' + @tableVar1) runs on 2008/2012, modified for 2005 and 2000 it also runs there (cannot assign in same line on 2005 and lower). sqlfiddle link: sqlfiddle.com/#!3/f82714/1

Andomar Over a year ago

You're right, the problem I remember is with exec sp_executesql 'select' + ' 1'. With exec it works fine.

Andomar · Accepted Answer · 2014-07-03 05:46:53Z

1

A variable table name requires dynamic SQL:

declare @table sysname
set @table = 'A'

declare @sql nvarchar(max)
set @sql = 'select * from T_' + @table + 'table1 '  +
    '    WHERE ref in IN (SELECT ref FROM T_' + @table + 'table2) ' +
    '          AND customer IN (SELECT customer FROM T_' + @table + 'table3)'

exec (@sql)

answered Jul 3, 2014 at 5:46

Andomar

239k55 gold badges387 silver badges412 bronze badges

Comments

Vikram Jain · Accepted Answer · 2014-07-03 05:59:02Z

1

--Just change a prefix, don't worry about sql query:

-- Where @prefix is a common a whatever is prifix of table
declare @prefix varchar(50); set @prefix='T_A';  
-- Here your query in string format      
declare @sql varchar(max); set @sql='';
set @sql='SELECT * FROM ' + @prefix + 'table1 WHERE ref IN (SELECT ref FROM ' + @prefix + 'table2)' +
+ ' AND customer IN (SELECT customer FROM ' + @prefix + 'table3)';
--execute your query
exec(@sql);

answered Jul 3, 2014 at 5:59

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Comments

Nadendla · Accepted Answer · 2014-07-03 05:49:39Z

0

Dynamic Sql Query:

declare @sqlcommand nvarchar(1000)

set @sqlcommand='<your sql query>'


exec(@sqlcommand)

answered Jul 3, 2014 at 5:49

Nadendla

7222 gold badges7 silver badges18 bronze badges

Comments

Jesuraja · Accepted Answer · 2014-07-03 06:16:12Z

0

Try this:

DECLARE @SQL VARCHAR(8000)
DECLARE @Part VARCHAR(10) = 'A'

SET @SQL = 'SELECT * FROM T_' + @Part + 'table1 
            WHERE  ref IN (SELECT ref FROM T_' + @Part + 'table2) AND
                   customer IN (SELECT customer FROM T_' + @Part + 'table3)'

EXEC (@SQL)

edited Jul 3, 2014 at 6:16

answered Jul 3, 2014 at 6:02

Jesuraja

3,8524 gold badges26 silver badges49 bronze badges

Collectives™ on Stack Overflow

Use of variable table name in Query

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related