Use variable in query for database name

Question

Is it possible to do something like this:

DECLARE @SourceDB VARCHAR(100);
SET @SourceDB = [DatabaseName]


INSERT INTO CompletedScope
( uidInstanceID ,
  completedScopeID ,
  state ,
  modified
)
SELECT uidInstanceID ,
       completedScopeID ,
       state ,
       modified
FROM SourceDB.[dbo].CompletedScope;

Basically use the declared SourceDB variable in the query.

Anytime you need to parameterize on an object name (column, table, database) etc. you need to use dynamic sql — Conrad Frix
– Conrad Frix, Commented Mar 4, 2014 at 18:44
You cant use table names like that,you need dynamic sql,put a string into a variable then execute it. — Mihai
– Mihai, Commented Mar 4, 2014 at 18:44

M.Ali · Accepted Answer · 2014-03-04 18:44:42Z

4

You will need to use Dynamic sql for this ...

DECLARE @SourceDB NVARCHAR(128), @Sql NVARCHAR(MAX);
SET @SourceDB = 'DatabaseName';


SET @Sql = N'INSERT INTO CompletedScope
                    ( uidInstanceID ,
                      completedScopeID ,
                      state ,
                      modified
                    )
            SELECT uidInstanceID ,
                   completedScopeID ,
                   state ,
                   modified
            FROM ' + QUOTENAME(@SourceDB) + '.[dbo].CompletedScope;'

EXECUTE sp_executesql @Sql

Use QUOTENAME() function when concatenating variables to your sql , protects you against Sql Injection attacks.

answered Mar 4, 2014 at 18:44

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

Comments

Avt · Accepted Answer · 2014-03-04 18:43:51Z

3

You can use a dynamic query

SELECT @sql=
'INSERT INTO CompletedScope
        ( uidInstanceID ,
          completedScopeID ,
          state ,
          modified
        )
SELECT uidInstanceID ,
       completedScopeID ,
       state ,
       modified
FROM ' + @SourceDB + '.[dbo].CompletedScope;'

EXEC sp_executesql @sql

answered Mar 4, 2014 at 18:43

Avt

17.1k4 gold badges54 silver badges73 bronze badges

Collectives™ on Stack Overflow

Use variable in query for database name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related