# Sql-server – use a table as a variable

sql servertable variable

I was wondering if there was a way to set a table name in SQL to be a variable.

I have a list of queries that hit the same table and didn't want to have to update them all so was hoping to declare the table name as a value.

Below is an example: what I wanted to do is declare the table name once and use the @tablename in the queries shown below.

/*at the field level */
SELECT
(
SELECT COUNT(DISTINCT PROVIDER_TIN) AS PROVIDER_TIN
from [Coloma].[dbo].Aetna_Medicare_2019  a
WHERE PROVIDER_TIN = '' OR PROVIDER_TIN IS NULL
) AS NULL_TINS,

(
SELECT COUNT(DISTINCT PROVIDER_ID) AS PROVIDER_ID
from [Coloma].[dbo].Aetna_Medicare_2019  a
WHERE PROVIDER_ID = '' OR PROVIDER_ID IS NULL
) AS NULL_IDS


I would recommend using a synonym.

Every time you need to change the Target table, you could do the following:

IF EXISTS(select * from sys.synonyms WHERE name=N'TARGET')
DROP SYNONYM [dbo].[TARGET]

SET @SQL = 'CREATE SYNONYM [dbo].[TARGET] FOR [Coloma].[dbo].Aetna_Medicare_2019'
EXEC sp_executesql @SQL


However, all your queries will always point to TARGET and won't need to be changed, since TARGET will act as an alias for a table you have already set up before:

SELECT COUNT(DISTINCT PROVIDER_ID) AS PROVIDER_ID FROM
[dbo].[TARGET] a
WHERE PROVIDER_ID = '' OR PROVIDER_ID IS NULL