Search code examples
sqlsql-serverdynamicclause

Avoid using dynamic from clause

How can i avoid using dynamic from clause? Even if i don't know the database name, i prefer to use a static statement, like this:

    select *
    into   #tempTable
    from   @DBName.Invoices
    where  InvoiceId = 5.

I got this error: Msg 102, Level 15, State 1, Line 6 Incorrect syntax near '.'.

I need to use select into clause because the column names may be different from each databases;

Thanks!

Solution

  • Unfortunately you will have to use dynamic SQL for this, see below for an example 

    Declare @DBNAME NVARCHAR(MAX) = 'xxx'
Declare @SQL NVARCHAR(MAX) ='select *
into   #tempTable
from   ' + @DBName + '.Invoices
where  InvoiceId = 5.'

execute sp_executesql @SQL