×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Drop a table based on the count of records in another table

Drop a table based on the count of records in another table

Drop a table based on the count of records in another table

(OP)
I run table updates daily. What I want to know is, can I drop table based on the count of another table?
So I run the query that is built with CTE's. The last CTE output is FINAL
What I want to do is -
if the count of FINAL is greater than 1
then
drop table PREV - this contains the data from 2 days ago.
drop table CURR - this contains the data from the previous day.
select * from FINAL into CURR
else
if FINAL not greater than 1 or query gets killed
then exit

RE: Drop a table based on the count of records in another table

Not really the answer to your question… but
That approach sounds like a very bad idea (read: bad data base design, IMO).
If you do DROP table(s), that means to me you also need to CREATE those tables often, right? Every day?
hairpull2

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Drop a table based on the count of records in another table

(OP)
Thanks Andrejeck. No the tables do not need to be re-created.
What approach would you take?
I used to drop the PREV table, move CURR to PREV, drop CURR, then results to CURR

RE: Drop a table based on the count of records in another table

Looks to me you have 3 tables with exactly the same structure (fields):
PREV - this contains the data from 2 days ago.
CURR - this contains the data from the previous day.
FINAL - today's data?

I would just have one table, let's name it BOB. And I would just add a DATE field (MY_DATE?) if it does not exist already. You could create a simple VIEWS that would equal your current code structure:
PREV_V  - Select * from BOB where MY_DATE = GETDATE() - 2
CURR_V  - Select * from BOB where MY_DATE = GETDATE() - 1
FINAL_V - Select * from BOB where MY_DATE = GETDATE()
 
If you don't care about the data older than 2 days, just DELETE it. (I would keep it. You never know if you will need it in the future...) smile

Quote (BobThornton)

No the tables do not need to be re-created.
On the contrary, that's what you do (re-create CURR table) by:
drop table CURR
select * from FINAL into CURR


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Drop a table based on the count of records in another table

(OP)
Thanks! The issue is the count of records is usually >200K so having that many tables with that much data is the issue.
The current process is automated using TOAD, so I want to keep it automated.
I currently have -
IF OBJECT_ID ('AdHocData.MDCOutEd.FL_MCD_Uni_Prev') IS NOT NULL
DROP TABLE AdHocData.MDCOutEd.FL_MCD_Uni_Prev

SELECT *
INTO AdHocData.MDCOutEd.FL_MCD_Uni_Prev
FROM AdHocData.MDCOutEd.FL_MCD_Uni_Curr

IF OBJECT_ID('AdHocData.MDCOutEd.FL_MCD_Uni_Curr') IS NOT NULL
DROP TABLE AdHocData.MDCOutEd.FL_MCD_Uni_Curr

query

SELECT *
INTO AdHocData.MDCOutEd.FL_MCD_Uni_Curr
FROM FINAL

The problem I am having is, one of the DBA's kills the process and/or the main table the query is pulling from is blank.
If the process is killed back to back nights, my CURR and PREV tables will be blank with no data to use until a successful run of the process happens

Sorry for being a pain!

RE: Drop a table based on the count of records in another table

You are not 'being a pain' smile If we cannot have a conversation here, where can you (we?) do it?
>200K records is nothing to Oracle, if you set your table right. But that's just my opinion.

But, any time you do any SELECT * INTO XYZ, if XYZ exists, you just insert new records, but if XYZ does NOT exist, you CREATE XYZ every time you run it.

>one of the DBA's kills the process
Well, if you DROP and CREATE tables a lot, your DBA may be ticked off. mad

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Drop a table based on the count of records in another table

BTW, Instead of DROP table, you may want to consider TRUNCATE table.
It is like DELETE * From Table, but you cannot do RollBack. But it takes a lot less resources that DROP and CREATE.

Just a suggestion...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Drop a table based on the count of records in another table

And isn't the whole job done by renaming the tables? That would make the least effort.
Or, let me ask: If you're done with the process, curr would become tomorrows prev data, right? If there would not be updates today that make some of it more recent. Why don't you then have a new empty table for each day?

All in all, I would never separate data that way, but if you do, why not take it to the end, logically? Then it would mean to drop prev, rename current to prev, rename today to current and create a new empty "today" table for the next day.

Chriss

RE: Drop a table based on the count of records in another table

With your approach, Chriss, I still would NOT do DROP nor CREATE:

Truncate prev, drop rename prev to temp, rename current to prev, rename today to current and create a new empty rename temp to "today" table for the next day.
wiggle

But, at the end of the day, we keep the same number of records: 3 tables or one.
One table would be my way to go. Delete what you don't need any more, insert new records. Done.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Drop a table based on the count of records in another table

(OP)
Thanks to both of you! You both are way over my head.
Chris, are you suggesting that I have one table - BOB
Nightly, run the process, and compare the nightly data to BOB and only insert those records that do not match?
Would I have to match all 70 fields from Nightly to Bob?
Thanks again for all of your help and guidance!

RE: Drop a table based on the count of records in another table

One table - BOB was my suggestion smile
You said you have >200K records (70 fields) per day?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Drop a table based on the count of records in another table

(OP)
Andrejek - yes

RE: Drop a table based on the count of records in another table

In my opinion, nobody (no human) can ‘consume’ 200K+ records of 70 fields of data per day. Especially when all of that is gone 3 days later. I do know that some system can produce this many records, that’s OK. But (that’s just my opinion) chances are – you may need just 1 record per day which could be the analyses of the 200K+ records: SUMs, averages, MINs, MAXes, etc. How all those 200K+ records are use now.
Wouldn’t that be nice…ponder

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Drop a table based on the count of records in another table

Bob, the major idea is that renaming tables instead of moving data from one to another table is a fast operation that doesn't shovel data around.

Isn't your problem, that the whole process is sometimes killed? If you do 3 or 5 or even 100 table renames, that takes about 0 seconds instead of moving data around. You overcome that problem instead of figuring out how to get to the final situation because your process was interrupted as too-long-running process. I can understand if you look in disbelief, but as long as the granularity of data you need to shovel around is full tables, then this can be done with a renaming instead.

Chriss

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close