SQL Server - select into from statement?

Question

I have this query in SQL Server:

select column
from table_53;

Now, I want to get this 53 from another table, so what I want to do is something like this:

select column
from table_(select id from table2);

Is there any way to do this in SQL Server?

I sense poor DB design here. But `sp_executesql` can help you here: https://msdn.microsoft.com/en-us/library/ms188001.aspx — trailmax, Jan 07 '16 at 14:44
Possible duplicate of [Table name as variable](http://stackoverflow.com/questions/2838490/table-name-as-variable) — JamieD77, Jan 07 '16 at 14:47
Thinking about this a bit more, I'm terrified of this design. And this http://blog.sqlauthority.com/2009/06/17/sql-server-list-schema-name-and-table-name-for-database/ can also help with the cludging. — trailmax, Jan 07 '16 at 14:47

score 1 · Answer 1 · answered Jan 07 '16 at 14:48

This is definitely not the way SQL thinks and works. Maybe your suggested approach can be mimicked by way of writing stored procedures in which you create SQL-statements which are then evaluated. However, this will not be very efficient.

A better approach would be to store the values of all your individual separate tables into one master table and mark them in a separate column tblid with their number (e.g. 53). Then you can always filter them from this master table by looking for this tblid.

Totally agree on the "SQL thinks and works". Just imagine what mess this "design" can create?! — trailmax, Jan 07 '16 at 14:50

score 0 · Accepted Answer · answered Jan 07 '16 at 14:47

You need dynamic sql query here.

declare @sqlQuery = 'select column
from table_(';

set @sqlQuery = @sqlQuery + 'select id from table2)';

EXEC (@sqlQuery)

Note :- One of cons of using dynamic sql query is sql injection. I would suggest to have better table structure or try to used parameterized query.

score 0 · Answer 3 · answered Jan 07 '16 at 14:54

Yes, you can, but using something like this:

DECLARE @ID INT;
DECLARE @QUERY NVARCHAR(MAX);

SELECT @ID = ID FROM TABLE_2;
--IF @ID EQUALS 53 THEN
SET @QUERY = 'SELECT COLUMN FROM TABLE_' + CAST(@ID AS NVARCHAR(10));
-- @QUERY EQUALS TO 'SELECT COLUMN FROM TABLE_53'
EXEC (@QUERY);

SQL Server - select into from statement?

3 Answers3