Consolidate Multiple Queries in an SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Consolidate Multiple Queries in an SP

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-08-22 : 11:06:20

I have a stored procedure which inserts like data from multiple company databases in our enterprise accounting package to a temporary table. Each database I pull from is in a seperate query, and the company where the data came from is inserted into the table by each individual query. The table structures from each database are identical. I am looking for a way to write the query once, and have the database I pull "from" be a variable.

This is an example of the method I currently use. (notice company field is different for each company DB.)

create #temptable
(
checknumber char(10),
checkamount money,
company char(5)
)

insert into #temptable

select checknumber, checkamount, 'OV' as Company

from data_001.dbo.checktable

where checktype = 3

----------

select checknumber, checkamount, 'AECC' as Company

from data_002.dbo.checktable

where checktype = 3

----------

select checknumber, checkamount, 'CRILP' as Company

from data_003.dbo.checktable

where checktype = 3

----------

ETC... FOR 36 COMPANIES.

SELECT * FROM #TEMPTABLE

DROP #TEMPTABLE

---

I have tried to just list the databases seperated by commas after the from, but there I can't find a way to differentiate the data and update the company field in the temp table when the query completes

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-22 : 12:40:48

Have you looked at sp_MSforeachdb? You might be able to play around with that, however, I think the way you are doing it now is going to be your best bet (and quite nicely illustrates why have multple like databases is usually a poor design choice.)

Jay White
{0}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-22 : 12:41:29

You could do it using dynamic sql and a cursor, but that would not be recommended as you'll lose a lot of performance. You are better off writing out the 36 queries.

Why is this data contained in multiple databases? You'd be better off having all of the data in one table and having an additional column in the table that would have the company name or some kind of identification for it. You'd then be able to do this in one query using a CASE statement.

Tara

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-08-22 : 12:45:25

THANKS, I have never used sp_MSforeachdb. This issue is the least of the DB design flaws in the database. It was originally in btrieve, and it really show. Performance is bad. Lot of duplicate data stored, and vanising data.

quote:
Originally posted by Page47

Have you looked at sp_MSforeachdb? You might be able to play around with that, however, I think the way you are doing it now is going to be your best bet (and quite nicely illustrates why have multple like databases is usually a poor design choice.)

Jay White
{0}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-22 : 12:47:41

Using sp_MSforeachdb is going to slow you down a little bit. You are better off writing out the 36 queries. Just because a stored procedure has 36 queries in it doesn't mean that it is not efficient.

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-22 : 20:32:42

quote:
It was originally in btrieve, and it really show. Performance is bad. Lot of duplicate data stored, and vanising data.

I feel your pain, you have no idea how much.

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-23 : 21:18:25

OK,

Your discussing temp (local) tables...which are only around as long as the transaction...

Why not create a view? And why do an INSERT? And what do you do with then data after it's stored?

CREATE VIEW v_myView
AS

select checknumber, checkamount, 'OV' as Company
from data_001.dbo.checktable
where checktype = 3
select checknumber, checkamount, 'AECC' as Company
from data_002.dbo.checktable
where whatever...
ect...

or can you not create views across db's?

I've been chillin along time...

Brett

8-)

SELECT POST=NewId()

EDIT: And I'm really interested in the "vanishing data" thing...

Damn, must be one of those miracle things again...I Hate when that happens..

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-08-25 : 09:15:48

Can not return more than one dataset, because I am using a crystal report to report the data.
Vanishing data just means, (at least in this case.) that there are tables which contain data about a transaction, until either it is posted, or a month(period) is closed. You then have summary info., but no detail on the transaction. In a case where I need detail, I have to write an sp that will write it to another table.

quote:
Originally posted by X002548

OK,

Your discussing temp (local) tables...which are only around as long as the transaction...

Why not create a view? And why do an INSERT? And what do you do with then data after it's stored?

CREATE VIEW v_myView
AS

select checknumber, checkamount, 'OV' as Company
from data_001.dbo.checktable
where checktype = 3
select checknumber, checkamount, 'AECC' as Company
from data_002.dbo.checktable
where whatever...
ect...

or can you not create views across db's?

I've been chillin along time...

Brett

8-)

SELECT POST=NewId()

EDIT: And I'm really interested in the "vanishing data" thing...

Damn, must be one of those miracle things again...I Hate when that happens..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources