-1

This question already has an answer here:

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

marked as duplicate by Paul White sql-server Apr 11 at 5:18

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    You can’t use a variable for an entity or identifier - you’ll need to use dynamic SQL. There are hundreds and hundreds of examples of this, but while you’re searching, please also read up on SQL injection. – Aaron Bertrand Apr 11 at 4:31
1

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
  • I added a Declare @SQL nvarchar(100) and it works perfectly now - thanks so much!! – Newelle Horn Apr 11 at 19:32
  • Please mark the answer as accepted. Thanks. – dsuy Apr 14 at 4:09

Not the answer you're looking for? Browse other questions tagged or ask your own question.