Using Database name in dynamic query in SQL Server

Last Reply one year ago By pandeyism

Posted one year ago

Mr.Muddasar

I need data from multipe database to compaine into single table for analysis purpose.

I have tried this

 

Declare @dbname varchar(50),@sqlCommand varchar(1000)
Set @dbname ='dbname'
set @sqlCommand = '
SELECT RegDate, SID, Name, CAddress, TelEM1, TelEM2, Relation, Disablity, Loan, TRStatus 
FROM' + @dbname + 'dbo.MemberRecord '

EXEC (@sqlCommand)

Error is Msg 102, Level 15, State 1, Line 15 Incorrect syntax near '.'.

 

if i using the below code, working fine

SELECT RegDate, SID, Name, CAddress, TelEM1, TelEM2, Relation, Disablity, Loan, TRStatus 
FROM [dbname].dbo.[MemberRecord] ORDER BY SID

Pls Advice me

Thank you

Maideen

Posted one year ago Modified on one year ago

Hi maideen,

Please refer below sample.

SQL

-- Without passing Schema name.
DECLARE @dbname varchar(50)
DECLARE @sqlCommand varchar(1000)
SET @dbname = 'test'
SET @sqlCommand = 'SELECT * FROM [' + @dbname +'].[dbo].[Customers]'
EXEC(@sqlCommand)
-- With Schema name.
DECLARE @dbname varchar(50) 
DECLARE @schema varchar(50)
DECLARE @sqlCommand varchar(1000)
SET @schema = 'dbo'
SET @dbname = 'test'
SET @sqlCommand = 'SELECT * FROM [' + @dbname +'].['+ @schema + '].[Customers]'
EXEC(@sqlCommand)

Output

CustomerId Name Country
1 John Hammond United States
2 Mudassar Khan India
3 Suzanne Mathews France
4 Robert Schidner Russia