Cursor from diff table -challenge

I have 17 tables with names as Breaktable1, Breaktable2,... till
Breaktable17

i make a query like this

set nocount on

Declare @.sEventtable varchar(20)
Declare @.sstartzoneid varchar(20)
Declare @.ssttopzoneid varchar(20)

Select @.sstartzoneid ='1'
Select @.ssttopzoneid = '17'

select @.sstartzoneid = convert(smallint,@.sstartzoneid)

if @.sstartzoneid<> 0
DECLARE Tablecursor FOR <---> ( error here )
select status, sum(cost)
FROM "breaktable " + @.sstartzoneid
where breakdate=DATEDIFF(day,'08/12/1960','03/29/2003')
group by status

CLOSE tableCursor
DEALLOCATE tableCursor

can somebody help ??You can't use a variable that way in the DECLARE CURSOR. You may have to declare the table dynamically.

Is now a good time to ask why you've got 17 tables when it seems that 1 would do quite nicely?

-PatP|||What do you want to do with the data accessed by the cursor? You can create a view comprised of all 17 tables with an artificially created field that would identify which table the data is coming from, and then do your cursor definition based on the value of that field. And of course, I agree with Pat 100%.|||I just want to read it for each day
so that i can just change the date everyday i run the query

Trust me it needed the 17 tables

I am from oracle so a little tizzy in sql|||Sorry, I don't trust anybody. I've seen 17-table implementation here, made by Anderson Consulting, and the company was paying them $180/hour. Just by looking at the names I can tell you that the design was not done by the person who understands either business or db design. How can I trust?|||I still can't see for the life of me any reason to have either 17 tables or a cursor. If those make you more comfortable, I guess that works Ok for me. I just can't see any reason for them.

-PatP|||I agree , but i did not designed it i just have to use it

so how do i make a query that will query all 17 tables and give me sum of cost and status

cursor or no cursor ?

table name break1 to break17|||I'd use:CREATE VIEW dbo.BigBreak AS
SELECT 1 AS table_id, * FROM dbo.breaktable1
UNION ALL SELECT 2, * FROM dbo.breaktable2
UNION ALL SELECT 3, * FROM dbo.breaktable3
UNION ALL SELECT 4, * FROM dbo.breaktable4
UNION ALL SELECT 5, * FROM dbo.breaktable5
UNION ALL SELECT 6, * FROM dbo.breaktable6
UNION ALL SELECT 7, * FROM dbo.breaktable7
UNION ALL SELECT 8, * FROM dbo.breaktable8
UNION ALL SELECT 9, * FROM dbo.breaktable9
UNION ALL SELECT 10, * FROM dbo.breaktable10
UNION ALL SELECT 11, * FROM dbo.breaktable11
UNION ALL SELECT 12, * FROM dbo.breaktable12
UNION ALL SELECT 13, * FROM dbo.breaktable13
UNION ALL SELECT 14, * FROM dbo.breaktable14
UNION ALL SELECT 15, * FROM dbo.breaktable15
UNION ALL SELECT 16, * FROM dbo.breaktable16
UNION ALL SELECT 17, * FROM dbo.breaktable17
GOI'm not sure how well the optimizer will process it, but I think that it will do Ok. You'll definitely need to test it first.

-PatP|||i went like this but takes about 30 mins to run

select status, sum(cost) as zone1
FROM event1
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by status

select status, sum(cost) as zone2
FROM event2
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by status

TILL 18

But takes about 35 mins for each run and slows all other user

and then i have to do shut down restart to make it work nicely ??

and the reason for 18 tables is that an event occurs and needs to be inserted(recorded) at 18 times at that exact moment (millisecond)|||How did you jump from 17 to 18 tables? Why on earth do you need to record extremely time sensitive information 18 different times?

-PatP|||we are an adsales company that insert ads on cable

it has to insert those breaks in 18 zones at the same exact time

whether its successfull or not it does an insert in each table.|||I am sure you can have a cursor within a cursor to do that|||A cursor within a cursor ...

I swear by the Holy book (guys here know what I mean) ... I would never even think of ever doing that ...|||we are an adsales company that insert ads on cable

it has to insert those breaks in 18 zones at the same exact time

whether its successfull or not it does an insert in each table.

Pat we got a CROSS THREAD JOIN going on here...

In any case a Timezone type column is all you needed...

Anderson Consulting...scrubs....

had to babysit everyone of them...

I saw one created a 7 level nested cursor...wondered why it ran 7 hours...|||besides the time their are about 15 other fields that needs to be inserted and they are different !!!!|||Are the structure (column type and order) the same for all (1..N) of the breakpoint tables? If not, could they be made the same?

-PatP|||the structure is the same

But about 40 people may be reading or writing
beside the auto writing these zones (18) do

so i taught its quiet busy

Tell me if some one is writing to a table lets say different rows does it locks the whole table

I understood as if they write to same rows than it will

Am I correct???|||No, you can have a practical infinity of inserts all going on at the same time without a problem in SQL-2000. This was a severe problem prior to SQL 6.5, it got much better with the IRL code in SQL 7.0, and it is a non-issue with SQL 2000.

In SQL-2000, you can only have one spid doing an UPDATE or a DELETE on any given row at any given time, but that is true of almost every database. We typically have 300-500 users simultaneously updating some of our heavily used tables at period end (but that is on pretty powerful hardware).

-PatP|||thanks but yes this is 6.5 i am working on
Perhaps then the designer was right ||||

I am not a fan of microsoft

For oracle
i would do a cursor very nicely

declare
i number
for i in 1 .. 17
loop
select *
from break + "i"|||As long as you aren't running 6.0 or earlier, you should be Ok. The problems were fixed (at least for the most part) in 6.5 as long as you don't explicitly turn IRL off!

This is just a suggestion, but 6.5 is at least five years old. That would be like using Oracle 7.0 (yes, there really was something before 7.3)! You really need to encourage your management to upgrade to current versions.

-PatP|||I don't understand what's wrong with this:

select table_id, status, sum(cost) as zone1
FROM dbo.BigBreak --event1 <--WHAT IS THIS?
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by table_id, status|||Nothing wrong actually
It was just a curiosity

I am about to upgrade to sql 2000

it usually takes about 4 months to get the things passed through accounts

thanks