declare cursor select from another database table

Question

Post reply

declare cursor select from another database table

setlan1983

SSCommitted

Points: 1636
More actions
October 8, 2009 at 2:58 am

#211988

I am writing a stored procedure, wish to pass in @DBName which can used for my declare cursor selection statement.
I tried "SELECT * FROM [@DBName].Table1", but cannot 🙁
May I know how to code that?
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You cannot use that syntax: the object to query cannot be a parameter.

You could use dynamic sql:

DECLARE @sql nvarchar(max)

SET @sql = 'SELECT * FROM ['+ @dbname +'].Table1'

EXEC sp_executesql @sql

Anyway you can't use dynamic sql as source for a cursor, so you would have to insert your data into a temp table and the open the cursor on the temp table:

CREATE TABLE #tempTable (

column1 int,

column2 int

)

DECLARE @sql nvarchar(max)

SET @sql = 'SELECT * FROM ['+ @dbname +']..Table1'

INSERT #tempTable

EXEC sp_executesql @sql

DECLARE CURSOR cur FOR SELECT * FROM #tempTable

It's a possible choiche when you don't have to open a cursor no a huge amount of data.

Another possibile solution is coding a query for each possible @dbName

DECLARE @cur CURSOR

DECLARE cur1 CURSOR FOR SELECT * FROM DB1..Table1

DECLARE cur2 CURSOR FOR SELECT * FROM DB2..Table1

IF @dbName = 'DB1'

SET @cur = cur1

IF @dbName = 'DB2'

SET @cur = cur2

Hope this helps

Gianluca

-- Gianluca Sartori

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

See if this helps

CREATE TABLE Table1 (

column1 int,

column2 int

)

INSERT INTO Table1(column1,column2) VALUES(10,20)

DECLARE @DBName VARCHAR(128)

DECLARE @column1 INT

DECLARE @column2 INT

SET @DBName=DB_Name()

EXEC ('DECLARE cur1 CURSOR FOR SELECT * FROM '+@DBName+'..Table1')

OPEN cur1

FETCH NEXT FROM cur1 INTO @column1,@column2

SELECT @column1,@column2

CLOSE cur1

DEALLOCATE cur1

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

setlan1983 SSCommitted Points: 1636 More actions · Answer 3

Mark-101232 (10/8/2009)
See if this helps
CREATE TABLE Table1 (
column1 int,
column2 int
)
INSERT INTO Table1(column1,column2) VALUES(10,20)
DECLARE @DBName VARCHAR(128)
DECLARE @column1 INT
DECLARE @column2 INT
SET @DBName=DB_Name()
EXEC ('DECLARE cur1 CURSOR FOR SELECT * FROM '+@DBName+'..Table1')
OPEN cur1
FETCH NEXT FROM cur1 INTO @column1,@column2
SELECT @column1,@column2
CLOSE cur1
DEALLOCATE cur1

If I have a WHERE clause with a variable @DTFrom? Is the following t-sql correct?

EXEC ('DECLARE cur1 CURSOR FOR SELECT * FROM '+@DBName+'..Table1 WHERE TransDT >= '+@DTFrom+'')

Btw, why need put double dot before "Table1"? Why not single dot?

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

setlan1983 (10/8/2009)
Mark-101232 (10/8/2009)
See if this helps
CREATE TABLE Table1 (
column1 int,
column2 int
)
INSERT INTO Table1(column1,column2) VALUES(10,20)
DECLARE @DBName VARCHAR(128)
DECLARE @column1 INT
DECLARE @column2 INT
SET @DBName=DB_Name()
EXEC ('DECLARE cur1 CURSOR FOR SELECT * FROM '+@DBName+'..Table1')
OPEN cur1
FETCH NEXT FROM cur1 INTO @column1,@column2
SELECT @column1,@column2
CLOSE cur1
DEALLOCATE cur1
If I have a WHERE clause with a variable @DTFrom? Is the following t-sql correct?
EXEC ('DECLARE cur1 CURSOR FOR SELECT * FROM '+@DBName+'..Table1 WHERE TransDT >= '+@DTFrom+'')
Btw, why need put double dot before "Table1"? Why not single dot?

If you have parameters (i.e. @DTFrom in your scenario), you're better off using sp_executesql instead of EXEC('...'), check in BOL for details.

Tables are fully qualified with database name *and* owner, not just database name.

Also in general, cursors are not the best approach because of performance/scalability issues, perhaps you could post some details of what you are trying to achieve.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

setlan1983 SSCommitted Points: 1636 More actions · Answer 5

Mark-101232 (10/8/2009)
setlan1983 (10/8/2009)
Mark-101232 (10/8/2009)
See if this helps
CREATE TABLE Table1 (
column1 int,
column2 int
)
INSERT INTO Table1(column1,column2) VALUES(10,20)
DECLARE @DBName VARCHAR(128)
DECLARE @column1 INT
DECLARE @column2 INT
SET @DBName=DB_Name()
EXEC ('DECLARE cur1 CURSOR FOR SELECT * FROM '+@DBName+'..Table1')
OPEN cur1
FETCH NEXT FROM cur1 INTO @column1,@column2
SELECT @column1,@column2
CLOSE cur1
DEALLOCATE cur1
If I have a WHERE clause with a variable @DTFrom? Is the following t-sql correct?
EXEC ('DECLARE cur1 CURSOR FOR SELECT * FROM '+@DBName+'..Table1 WHERE TransDT >= '+@DTFrom+'')
Btw, why need put double dot before "Table1"? Why not single dot?
If you have parameters (i.e. @DTFrom in your scenario), you're better off using sp_executesql instead of EXEC('...'), check in BOL for details.
Tables are fully qualified with database name *and* owner, not just database name.
Also in general, cursors are not the best approach because of performance/scalability issues, perhaps you could post some details of what you are trying to achieve.

Actually I have 2 database, DB_A & DB_B.

I want to Sum(SalesAmt) at Sales table in DB_A and insert into DB_B rptSales table.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

setlan1983 (10/8/2009)
Mark-101232 (10/8/2009)
setlan1983 (10/8/2009)
Mark-101232 (10/8/2009)
See if this helps
CREATE TABLE Table1 (
column1 int,
column2 int
)
INSERT INTO Table1(column1,column2) VALUES(10,20)
DECLARE @DBName VARCHAR(128)
DECLARE @column1 INT
DECLARE @column2 INT
SET @DBName=DB_Name()
EXEC ('DECLARE cur1 CURSOR FOR SELECT * FROM '+@DBName+'..Table1')
OPEN cur1
FETCH NEXT FROM cur1 INTO @column1,@column2
SELECT @column1,@column2
CLOSE cur1
DEALLOCATE cur1
If I have a WHERE clause with a variable @DTFrom? Is the following t-sql correct?
EXEC ('DECLARE cur1 CURSOR FOR SELECT * FROM '+@DBName+'..Table1 WHERE TransDT >= '+@DTFrom+'')
Btw, why need put double dot before "Table1"? Why not single dot?
If you have parameters (i.e. @DTFrom in your scenario), you're better off using sp_executesql instead of EXEC('...'), check in BOL for details.
Tables are fully qualified with database name *and* owner, not just database name.
Also in general, cursors are not the best approach because of performance/scalability issues, perhaps you could post some details of what you are trying to achieve.
Actually I have 2 database, DB_A & DB_B.
I want to Sum(SalesAmt) at Sales table in DB_A and insert into DB_B rptSales table.

Something like this then (no cursors)

INSERT INTO DB_B..rptSales(SalesAmt,...)

SELECT Sum(SalesAmt),...

FROM DB_A..Sales

WHERE TransDT >= @DTFrom

GROUP BY...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

setlan1983 SSCommitted Points: 1636 More actions · Answer 7

Mark-101232 (10/8/2009)
setlan1983 (10/8/2009)
Mark-101232 (10/8/2009)
setlan1983 (10/8/2009)
Mark-101232 (10/8/2009)
See if this helps
CREATE TABLE Table1 (
column1 int,
column2 int
)
INSERT INTO Table1(column1,column2) VALUES(10,20)
DECLARE @DBName VARCHAR(128)
DECLARE @column1 INT
DECLARE @column2 INT
SET @DBName=DB_Name()
EXEC ('DECLARE cur1 CURSOR FOR SELECT * FROM '+@DBName+'..Table1')
OPEN cur1
FETCH NEXT FROM cur1 INTO @column1,@column2
SELECT @column1,@column2
CLOSE cur1
DEALLOCATE cur1
If I have a WHERE clause with a variable @DTFrom? Is the following t-sql correct?
EXEC ('DECLARE cur1 CURSOR FOR SELECT * FROM '+@DBName+'..Table1 WHERE TransDT >= '+@DTFrom+'')
Btw, why need put double dot before "Table1"? Why not single dot?
If you have parameters (i.e. @DTFrom in your scenario), you're better off using sp_executesql instead of EXEC('...'), check in BOL for details.
Tables are fully qualified with database name *and* owner, not just database name.
Also in general, cursors are not the best approach because of performance/scalability issues, perhaps you could post some details of what you are trying to achieve.
Actually I have 2 database, DB_A & DB_B.
I want to Sum(SalesAmt) at Sales table in DB_A and insert into DB_B rptSales table.
Something like this then (no cursors)
INSERT INTO DB_B..rptSales(SalesAmt,...)
SELECT Sum(SalesAmt),...
FROM DB_A..Sales
WHERE TransDT >= @DTFrom
GROUP BY...

But if I want archive the following:

1. "SELECT Sum(SalesAmt) AS TotalSales, COUNT(*) AS TotalTrans FROM DB_A.Sales WHERE TransDT >= @DTFrom"

2. "COUNT(*) AS TotalPassTrans FROM DB_A.Sales WHERE TransDT >= @DTFrom AND (DATEDIFF(minute, EntryDT, TransDT) <= 15)"

3. I need to "INSERT INTO DB_B.rptSales (SalesAmt, TotalTrans, TotalPassTrans)" where TotalTrans = TotalTrans - TotalPassTrans

Can do that without using cursor? Please guide.

WayneS SSC Guru Points: 95460 More actions · Answer 8

setlan1983 (10/8/2009)
Btw, why need put double dot before "Table1"? Why not single dot?...
But if I want archive the following:
1. "SELECT Sum(SalesAmt) AS TotalSales, COUNT(*) AS TotalTrans FROM DB_A.Sales WHERE TransDT >= @DTFrom"
2. "COUNT(*) AS TotalPassTrans FROM DB_A.Sales WHERE TransDT >= @DTFrom AND (DATEDIFF(minute, EntryDT, TransDT) <= 15)"
3. I need to "INSERT INTO DB_B.rptSales (SalesAmt, TotalTrans, TotalPassTrans)" where TotalTrans = TotalTrans - TotalPassTrans
Can do that without using cursor? Please guide.

1st, you asked why you need a double-dot. A fully-qualified object name has the syntax of:

[servername].[database name].[schema name].

the first three are optional; however once one is needed, the others are needed. So once you specify the database name (DB_A), you need the schema name. The double-dot allows sql to look up the schema you're allowed to see the table under, it's essentially just a short-cut for dbo in this case. Below, I've used dbo to specify the schema.

Now, about the queries... You've just about got it.

SELECT Sum(SalesAmt) AS TotalSales,

COUNT(*) AS TotalTrans

FROM DB_A.dbo.Sales

WHERE TransDT >= @DTFrom

SELECT COUNT(*) AS TotalPassTrans

FROM DB_A.dbo.Sales

WHERE TransDT >= @DTFrom

AND (DATEDIFF(minute, EntryDT, TransDT) >= 15)

INSERT INTO DB_B.dbo.rptSales (SalesAmt, TotalTrans, TotalPassTrans)

SELECT SalesAmt, TotalTrans, TotalPassTrans

FROM DB_A.dbo.Sales

WHERE TotalTrans = TotalTrans - TotalPassTrans

However, the last one will only insert anything where TotalPassTran = 0, so I don't think this is what you're looking for.

In the last line, is the "TotalTrans - TotalPassTrans" coming from the first two queries? If so, then you need to put those values into variables, and use the variables in the third query. Like so...

DECLARE @TotalTrans int, @TotalPassTrans int

SELECT @TotalTrans = COUNT(*)

FROM DB_A.dbo.Sales

WHERE TransDT >= @DTFrom

SELECT @TotalPassTrans = COUNT(*)

FROM DB_A.dbo.Sales

WHERE TransDT >= @DTFrom

AND (DATEDIFF(minute, EntryDT, TransDT) >= 15)

INSERT INTO DB_B.dbo.rptSales (SalesAmt, TotalTrans, TotalPassTrans)

SELECT SalesAmt, @TotalTrans, @TotalPassTrans

FROM DB_A.dbo.Sales

WHERE TotalTrans = @TotalTrans - @TotalPassTrans

and, just to be a wee bit more efficient, you can combine those first two queries into one:

SELECT @TotalTrans = COUNT(*)

@TotalPassTrans = SUM(CASE WHEN DATEDIFF(minute, EntryDT, TransDT) >= 15

THEN 1 ELSE 0 END)

FROM DB_A.dbo.Sales

WHERE TransDT >= @DTFrom

If you have an index on TransDT, and include EntryDT, then this ought to be pretty fast.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

setlan1983 SSCommitted Points: 1636 More actions · Answer 9

WayneS (10/8/2009)
setlan1983 (10/8/2009)
Btw, why need put double dot before "Table1"? Why not single dot?...
But if I want archive the following:
1. "SELECT Sum(SalesAmt) AS TotalSales, COUNT(*) AS TotalTrans FROM DB_A.Sales WHERE TransDT >= @DTFrom"
2. "COUNT(*) AS TotalPassTrans FROM DB_A.Sales WHERE TransDT >= @DTFrom AND (DATEDIFF(minute, EntryDT, TransDT) <= 15)"
3. I need to "INSERT INTO DB_B.rptSales (SalesAmt, TotalTrans, TotalPassTrans)" where TotalTrans = TotalTrans - TotalPassTrans
Can do that without using cursor? Please guide.
1st, you asked why you need a double-dot. A fully-qualified object name has the syntax of:
[servername].[database name].[schema name].
the first three are optional; however once one is needed, the others are needed. So once you specify the database name (DB_A), you need the schema name. The double-dot allows sql to look up the schema you're allowed to see the table under, it's essentially just a short-cut for dbo in this case. Below, I've used dbo to specify the schema.
Now, about the queries... You've just about got it.
SELECT Sum(SalesAmt) AS TotalSales,
COUNT(*) AS TotalTrans
FROM DB_A.dbo.Sales
WHERE TransDT >= @DTFrom
SELECT COUNT(*) AS TotalPassTrans
FROM DB_A.dbo.Sales
WHERE TransDT >= @DTFrom
AND (DATEDIFF(minute, EntryDT, TransDT) >= 15)
INSERT INTO DB_B.dbo.rptSales (SalesAmt, TotalTrans, TotalPassTrans)
SELECT SalesAmt, TotalTrans, TotalPassTrans
FROM DB_A.dbo.Sales
WHERE TotalTrans = TotalTrans - TotalPassTrans
However, the last one will only insert anything where TotalPassTran = 0, so I don't think this is what you're looking for.
In the last line, is the "TotalTrans - TotalPassTrans" coming from the first two queries? If so, then you need to put those values into variables, and use the variables in the third query. Like so...
DECLARE @TotalTrans int, @TotalPassTrans int
SELECT @TotalTrans = COUNT(*)
FROM DB_A.dbo.Sales
WHERE TransDT >= @DTFrom
SELECT @TotalPassTrans = COUNT(*)
FROM DB_A.dbo.Sales
WHERE TransDT >= @DTFrom
AND (DATEDIFF(minute, EntryDT, TransDT) >= 15)
INSERT INTO DB_B.dbo.rptSales (SalesAmt, TotalTrans, TotalPassTrans)
SELECT SalesAmt, @TotalTrans, @TotalPassTrans
FROM DB_A.dbo.Sales
WHERE TotalTrans = @TotalTrans - @TotalPassTrans
and, just to be a wee bit more efficient, you can combine those first two queries into one:
SELECT @TotalTrans = COUNT(*)
@TotalPassTrans = SUM(CASE WHEN DATEDIFF(minute, EntryDT, TransDT) >= 15
THEN 1 ELSE 0 END)
FROM DB_A.dbo.Sales
WHERE TransDT >= @DTFrom
If you have an index on TransDT, and include EntryDT, then this ought to be pretty fast.

But in the above case, i wish to set DB_A as a variable that can be passed in. How to achieve that?

Garadin One Orange Chip Points: 29613 More actions · Answer 10

setlan1983 (10/8/2009)
But in the above case, i wish to set DB_A as a variable that can be passed in. How to achieve that?

You can do it with dynamic SQL if you have many databases, or if you have only a few, you can do it with IF statements.

IE.

IF @DB = 'DB1'

BEGIN

SELECT ...

FROM DB1.dbo.Sales ...

END

IF @DB = 'DB2'

etc. etc.

If you have a large number of databases, or you want to make this code applicable to new databases in the future, dynamic SQL, along with the code that the above posters have given you should fix your issue.

Dynamic SQL would turn Wayne's query into something like the following, which allows you to pass in the variable db name.

DECLARE @SQL varchar(max)

SET @SQL = '

SELECT @TotalTrans = COUNT(*)

@TotalPassTrans = SUM(CASE WHEN DATEDIFF(minute, EntryDT, TransDT) >= 15

THEN 1 ELSE 0 END)

FROM ' + @DB_A + '.dbo.Sales

WHERE TransDT >= @DTFrom'

EXEC(@SQL)

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]