Below, is a sample excerpt of a reoccurring code I run monthly. Each run, a new table is created based on the Month. For example, the created table name isYEAR2016..report_Nov2016, but for next month the tablename will end in _Dec2016. Instead of find&replace to change all table names(10 occurrences), I want to have one declare variable at the beginning of my code so that I only have to change the table name once and it gets reflected throughout the code.
declare @table varchar(30)
set @table ='YEAR2016..report_Nov2016'
select recipient_ID, recipient_Name, recipient_DOB,program_code
into @table
from YEAR2016..claim_table
Separate batches
alter table @table
add Medicaid char(1)
GO
update @table set Medicaid =''
GO
update @table set Medicaid ='Y' where program_code ='MK A'
From what I understand, declare @table table () should be used, but that won't accomplish my intention, as a table needs to be created in the database. I'm also aware,with declare table type, into @table won't work and needs to be Insert into @table.
Is it possible in TSQL to do what I'm envisioning?
Best Answer
In SQL Server, you need to keep table names and column names static in code that isn't dynamic.
To do what you are trying to do, you'd have to use Dynamic SQL - declare variables for your SQL Statement, populate the SQL statement dynamically - using your @TableName variable to supply the table name - then execute the dynamic SQL.
There are a lot of gotchas to think about with Dynamic SQL. A good place to start is a rather lengthy piece on the good and bad side of Dynamic SQL from Erland Sommarskog in this article, "The Curse and Blessings of Dynamic SQL"