Home » SQL & PL/SQL » SQL & PL/SQL » creating table automatically with sysdate every new day
creating table automatically with sysdate every new day [message #272757] Sun, 07 October 2007 01:58 Go to next message
arunanamika2000
Messages: 1
Registered: October 2007
Location: pune
Junior Member
want to create table with following naming convention

RRCUSTyyyymmdd

I want this script to be scheduled and table is created automatically by SELECT data from other table

i.e. create table ACGUJyyyymmdd as
select * from <table_name>
where <condition>


Re: creating table automatically with sysdate every new day [message #272758 is a reply to message #272757] Sun, 07 October 2007 02:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And what did YOU try so far?
We (and your boss) expect you to do the work; we will only advise, but after you show us you tried and told us where you got stuck.
Re: creating table automatically with sysdate every new day [message #272765 is a reply to message #272758] Sun, 07 October 2007 03:15 Go to previous messageGo to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Why would you want to create a new table every day? Would it not be wiser to jut have one table and then on a daily basis at say 00:00 update the table with what ever your where is and then have a column with the sysdate? So when you want to query you can do so by the column containing the date, sure you can even add an index on that column so if there is alot of info then it will run quicker.
Re: creating table automatically with sysdate every new day [message #273002 is a reply to message #272757] Mon, 08 October 2007 10:04 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Or even better, you can create a partitioned table that will have a new partition for every day. creating a new table every day is just very bad programming.
Re: creating table automatically with sysdate every new day [message #273079 is a reply to message #273002] Tue, 09 October 2007 01:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
My vote goes to the partitioned table.

MHE
Re: creating table automatically with sysdate every new day [message #273109 is a reply to message #273079] Tue, 09 October 2007 02:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Depends if you've paid for partitioning or not really.

But creating a new table every day is just bad practice.
Re: creating table automatically with sysdate every new day [message #273112 is a reply to message #273109] Tue, 09 October 2007 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes I can't imagine the query if you want to get the rows from the past 42 days!

Regards
Michel
Re: creating table automatically with sysdate every new day [message #273122 is a reply to message #273112] Tue, 09 October 2007 02:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Perhaps they could dynamically create a unified view over all those tables every day as well.
Laughing
Re: creating table automatically with sysdate every new day [message #273134 is a reply to message #273122] Tue, 09 October 2007 03:06 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why create tables or partitions anyway, when all you do is COPY from the original table?
Miight as well query on the original. (maybe add a column containing trunc(date) or add a FBI on trunc(date))
Previous Topic: comma separated output
Next Topic: Save PDF or Image File in Database
Goto Forum:
  


Current Time: Fri Dec 09 00:01:09 CST 2016

Total time taken to generate the page: 0.07975 seconds