CODEWITHSUNDEEP
sqlsql-server
user2869233
user2869233

Reputation: 111

How to form a table name from concatenating strings in Select Statement in SQL 2012

I want to achieve this - 

SELECT * FROM A1234

I have the the ID 1234 saved in another table called Aliases which has two columns Alias,ID with one record like this.

Alias = TestTable, ID = 1234

So I am trying something like this

SELECT * FROM ('A'+ (SELECT ID FROM Aliases WHERE Alias = 'TestTable'))

Any help would be appreciated

Upvotes: 5

Views: 22400

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280423

You need dynamic SQL for this.

DECLARE @sql NVARCHAR(MAX);

SELECT TOP (1) @sql = N'SELECT * FROM A' + RTRIM(ID) + ';' 
  FROM dbo.Aliases WHERE Alias = 'TestTable';

EXEC sp_executesql @sql;

To build a set of statements that selects all of them, you can say:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
  SELECT *, ''A' + RTRIM(ID) + ''' FROM A' + RTRIM(ID) + ';'
FROM dbo.Aliases;

PRINT @sql;

-- EXEC sp_executesql @sql;

Upvotes: 5

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

You should use dynamic sql.

DECLARE @Q VARCHAR(MAX),@ID INT
SET @ID=(Select DISTINCT ID from Aliases where Alias = 'TestTable') -- CHECK TO RETURN JUST ON RESULT
SET @Q='SELECT * FROM A'+CAST(@ID AS VARCHAR(10))
EXEC(@Q)

Upvotes: 5

Related Questions