Clever Geek Handbook

INSERT INTO with variable for DB

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

    , fieldaSubject

I've 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 an error:

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


source to share


1 answer 


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


+4


source






More articles:


All Articles