Using Database name in dynamic query in SQL Server

maideen

SOLVED CLOSED ACTIVE ANSWERED
User: maideen
Posted: on Jul 27, 2018 10:53 PM
Forum: SQL Server
Answer: 1
Views: 1550

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

Download FREE API for Word, Excel and PDF in ASP.Net

pandeyis...

0 3

Reply
ANSWER
User: pandeyism
Replied: on Jul 30, 2018 04:40 AM
Modified: on Jul 30, 2018 04:42 AM
Report

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Using Database name in dynamic query in SQL Server

Company

Explore

Follow us

Contact Us