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.

Josh Stevens · Accepted Answer · 2018-07-11 23:27:20Z

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

answered Jul 11, 2018 at 23:27

Josh Stevens

4,2811 gold badge20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gordy · Accepted Answer · 2018-07-14 21:19:47Z

1

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 ....

edited Jul 14, 2018 at 21:19

answered Jul 11, 2018 at 23:34

gordy

9,9713 gold badges37 silver badges49 bronze badges

1 Comment

amantur Over a year ago

This is better option than generating full dynamic SQL string.

Collectives™ on Stack Overflow

SQL Dynamic Table insert SQL Server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related