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