INSERT INTO with variable for DB

Question

I want to insert:

to database TEST, table SUBJECT, field aSubject when @Database = 'TS'
to database TEST1, table SUBJECT, field aSubject when @Database = 'TS1'
else to database DEMO, table SUBJECT, field aSubject

I tried this:

DECLARE @Database varchar(10)
Set @Database = 'TS'

INSERT INTO
(
CASE
WHEN @Database = 'TS' THEN 'TEST.dbo.SUBJECT' 
WHEN @Database = 'TS1' then 'TEST1.dbo.SUBJECT'
ELSE 'DEMO.dbo.SUBJECT' END
) (aSubject)
SELECT 'Company'

I get error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.

A table or column name cannot be a variable. In effect you have to create a new SQL statement that expands the variables. One way to do that is dynamic SQL, like declare @sql varchar(max) = 'select ...'; exec @sql; — Andomar
– Andomar, Commented Aug 29, 2014 at 10:56

M.Ali · Accepted Answer · 2014-08-29 11:03:01Z

4

DECLARE @Database SYSNAME;
Set @Database = 'TS'

SET @Database = CASE
                    WHEN @Database = 'TS'  THEN 'TEST' 
                    WHEN @Database = 'TS1' THEN 'TEST1'
                    ELSE 'DEMO' 
                END


DECLARE @sql NVARCHAR(MAX);


SET @sql= N' INSERT INTO ' + QUOTENAME(@Database) + N'.[dbo].[SUBJECT] '
        + N' SELECT ''Company'' '

EXECUTE sp_executesql @sql

answered Aug 29, 2014 at 11:03

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

Collectives™ on Stack Overflow

INSERT INTO with variable for DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related