SQL Dynamic Table insert SQL Server

Question

I have a process that sends variables to a database push. Depending on 1 variable - the table name changes to where the information is pushed.

I.E. Table A has 3 columns and table B has 3 columns. My attempt at a dynamic insert using IIF looks like this:

DECLARE @TableName VARCHAR(100)
SET @TableName = (SELECT IIF(${@var1}$ = 'A', 'TABLE_A', 'Table_B'))
SELECT @TableName
INSERT INTO @TableName (Var2_Name, Created_Date, Deleted_Date) 
VALUES (@Var2, getutcdate(), getutcdate());

The first SELECT @TableName shows me the value of the IIF but when I try using it in the insert, it fails and says

Must declare the table variable @TableName.

Is there a way for it to recognize the table name when inserting or is there another way to do a dynamic insert.

Answer 1

If the query is static your tables and columns names must be static too.

For dynamic table or column names, you should generate the full SQL dynamically, and use sp_executesql to execute it.

Something like this should do it, sorry if I made a syntax error, coded on the fly.

DECLARE @TableName varchar(100) = (SELECT IIF(${@var1}$ = 'A', 'TABLE_A', 'Table_B'))
SELECT @sql = 
N 'INSERT INTO ' + @TableName + '(Var2_Name, Created_Date, Deleted_Date) ' + 
N 'VALUES (' + @Var2 + ', getutcdate(), getutcdate())'
EXEC sp_executesql @sql

Answer 2

you could two inserts

insert into TABLE_A select @Var2, getutcdate(), getutcdate() where @var1 = 'A'
insert into Table_b select @Var2, getutcdate(), getutcdate() where @var1 = 'B'

..or use a condition, your case doesn't require dynamic sql, e.g.:

if (@var1 = 'A')
 insert into Table_A ....
else
 insert into Table_B ....

• TheVillageIdiot: This is better option than generating full dynamic SQL string.