Is it possible to use concat in FROM clause of a SQL statement?

Question

I have a couple of tables in my database and I build a query to combine data from them and store that data in a temporary table on which I use as input for other queries. But since a new delivery will be added to te database on a daily basis, I would like to only change one variable, instead of going through my code and adjust every FROM clause.

Is it possible to use a concat function inside a FROM clause so that it will select the required table based on the input variable? Like:

DECLARE @colTableUP TABLE (
    x varchar(50)
    ,y varchar(50)
    ,z varchar(50)
)

DECLARE @version AS string
SET @version = '20160620'

INSERT INTO @colTableUP
SELECT x, y, z
  FROM CONCAT('[dbo].[Table1_', @version ,']') 
UNION ALL
SELECT x, y, z
  FROM CONCAT('[dbo].[Table2_', @version ,']') 
UNION ALL

etc...

Since I don't get the code working I would like to know if it is possible at all to use CONCAT inside FROM, or is there any other best practice to achieve the required result instead of adjusting it all manually?

No, you'll need to use dynamic SQL for what you're trying to do. — Siyual, Jun 20 '16 at 18:54
If you want to use a variable for the table name you're going to have to build up the SQL in a string variable and use `sp_executesql` — D Stanley, Jun 20 '16 at 18:54
From your sample, you shouldn't have a new table with every new version (day?) - instead, you should just have a version column, and filter as needed. `select x, y, z from Table1 where version = @version` — Joe Enos, Jun 20 '16 at 18:57
Possible duplicate of [Table name as variable](http://stackoverflow.com/questions/2838490/table-name-as-variable) — Tab Alleman, Jun 20 '16 at 18:58
Hi Joe Enos, Table1 and Table2 are different datasets of which I need data from, so adding a version column will not work in this case. — ArBro, Jun 20 '16 at 19:00

score 3 · Answer 1 · answered Jun 20 '16 at 19:04

This is too long for a comment.

First, having multiple tables with the same columns is suspicious. It is better to load all the data into a single table, with the "version" as a column name.

If the data is being created as a separate table, then you can modify the table creation job load the data into the "master" table. If you really want to get fancy, you can use a DDL trigger, so when the table is created it gets loaded automatically.

A master table with all the rows is one option. Another is a view that does the union all query in the question. For this, you would drop and re-create the view, or use alter view.

score 1 · Answer 2 · answered Jun 20 '16 at 21:16

This can be accomplished by Dynamic SQL below:

DECLARE @SqlScript  AS Varchar(max)
DECLARE @version AS Varchar(10)
SET @version = '20160620'


SET @SqlScript = '

DECLARE @colTableUP TABLE (
x varchar(50)
,y varchar(50)
,z varchar(50)
)

INSERT INTO @colTableUP
SELECT x, y, z
  FROM [dbo].[Table1_' + @version + '] 
UNION ALL
SELECT x, y, z
  FROM [dbo].[Table2_' + @version + ']
UNION ALL
SELECT x, y, z
  FROM [dbo].[Table3_' + @version + ']
UNION ALL
SELECT x, y, z
  FROM [dbo].[Table4_' + @version + ']'

--SELECT @SqlScript
EXEC (@SqlScript);

Is it possible to use concat in FROM clause of a SQL statement?

2 Answers2