473,326 Members | 2,168 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Multiple db query call from within different context into #temp table

The first query returns me the results from multiple databases, the
second does the same thing except it puts the result into a #temp
table? Could someone please show me an example of this using the first
query? The first query uses the @exec_context and I am having a
challenge trying to figure out how to make the call from within a
different context and still insert into a #temp table.

DECLARE @exec_context varchar(30)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @exec_context = @DBNAME + '.dbo.sp_executesql '
set @sql = N'select top 10 * from products'
exec @exec_context @sql
FETCH NEXT FROM companies_cursor INTO @DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
-------------------------------------------------------------------------------------
CREATE TABLE #Test (field list here)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)

DECLARE companies_cursor CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULL
ORDER BY NAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'
INSERT INTO #Test
exec (@sql)
FETCH NEXT FROM companies_cursor INTO @DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
SELECT * from #Test
DROP TABLE #Test

Mar 21 '07 #1
1 4130
On Mar 21, 12:57 pm, santaferub...@gmail.com wrote:
The first query returns me the results from multiple databases, the
second does the same thing except it puts the result into a #temp
table? Could someone please show me an example of this using the first
query? The first query uses the @exec_context and I am having a
challenge trying to figure out how to make the call from within a
different context and still insert into a #temp table.

DECLARE @exec_context varchar(30)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @exec_context = @DBNAME + '.dbo.sp_executesql '
set @sql = N'select top 10 * from products'
exec @exec_context @sql
FETCH NEXT FROM companies_cursor INTO @DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
---------------------------------------------------------------------------*----------
CREATE TABLE #Test (field list here)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)

DECLARE companies_cursor CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULL
ORDER BY NAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'
INSERT INTO #Test
exec (@sql)
FETCH NEXT FROM companies_cursor INTO @DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
SELECT * from #Test
DROP TABLE #Test
Not sure if this solves the problem:

WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = ' INSERT INTO #Test select top 10 * from '+@DBNAME
+'.dbo.products'
exec (@sql)
FETCH NEXT FROM companies_cursor INTO @DBNAME
END

Mar 21 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to...
9
by: Ed_No_Spam_Please_Weber | last post by:
Hello All & Thanks in advance for your help! Background: 1) tblT_Documents is the primary parent transaction table that has 10 fields and about 250,000 rows 2) There are 9 child tables with...
3
by: Jason | last post by:
I was given a database that has unique IDs for each record such as: AL00001 AL00002 AL00003 etc The trouble is, I'm going to need to import data from another source that will also have IDs...
9
by: Graham | last post by:
I have been having some fun learning and using the new Controls and methods in .Net 2.0 which will make my life in the future easier and faster. Specifically the new databinding practises and...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
3
by: TomH | last post by:
I am using VB within VS 2005. How can I have multiple datagridviews on the same form pulling from the same table? e.g. I want to see all clients from New York in one view and all clients from LA in...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
5
by: rdemyan via AccessMonster.com | last post by:
I have a need to add another field to all of my tables (over 150). Not data, but an actual field. Can I code this somehow. So the code presumabley would loop through all the tables, open each...
7
by: =?Utf-8?B?TG9zdEluTUQ=?= | last post by:
Hi All :) I'm converting VB6 using True DBGrid Pro 8.0 to VB2005 using DataGridView. True DBGrid has a MultipleLines property that controls whether individual records span multiple lines. Is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.