Data load

[sameer11]

Hi All,

I haven't done anything like this before. Any help is apperciated.

Using Oracle 9i DB. We have a table which has 100,000 rows. I need to increase the volume of the table to insert 1 million rows. his is a test envi. I need to take the 100,000 rows change the date field to different dates and do insert to make the table populate 1 million rows. How can I do this?

Thanks in Advance,
Sameer

 

[Thargy]

Sameer,

assuming your date field is of the type 'date' then you can achieve what you want with a couple of simple inserts.

insert into table sameer
  (select column1, column2, date_column +1, column3
     from sameer);
COMMIT;

If your table started with 100,000 rows, it will now have 200,000, but the latest 100,000 will have a date one day greater than the first 100,000.

Repeat the above insert 8 more times and you've got your million rows. It's up to you to take care of the primary key values and make sure that there are no constraint violations etc. without knowing more about the precise details of the table involved, I can't say more.

If you post the exact structure of the table, any constraints and some sample data, I could be more helpful.

Regards

T

Grinding away at things Oracular

 

[sameer11]

Thank you, Iam not really concered about the repeated data in the table, since i need this to test a reports with huge row returns.

Thanks again!

 

[Beilstwh]

Actually you only need to repeat this 4 times to get 1,600,000 rows.

100,000 + 100,000 = 200,000
200,000 + 200,000 = 400,000
400,000 + 400,000 = 800,000
800,000 + 800,000 = 1,600,000

Bill
Oracle DBA/Developer
New York State, USA

 

[sameer11]

Yes, I realized that before. But I framed my question in a wrong way. I need something where I loop it and use this for various other tables and can be repeated again.

I need to do a insert into table_A for a business day (excluding the sat,sun and holidays...which i can use the calender table to check if it is Busday)

5000 rows per day which makes 10000 for 20 days and for 3 months it make 30000 rows

starting on a max date in the table_A and adding a day to it I need to insert the data into table_A for next 3-5 months

How can I do this fast and in best possible way



CREATE TABLE calender
(
CYCLE_DATE DATE NOT NULL,
BUSN_DAY_FLG VARCHAR2(1 BYTE) NOT NULL,
HOLIDAY_FLG VARCHAR2(1 BYTE) NOT NULL)


CREATE TABLE table_A
(
CYCLEDATE DATE NOT NULL,
ACCOUNT_ID CHAR(9 BYTE) NOT NULL,
CUSTODY_ACCOUNT_ID CHAR(4 BYTE) NOT NULL,
ASSET_ID VARCHAR2(9 BYTE) NOT NULL,
VAULT VARCHAR2(10 BYTE) NOT NULL,
OPENING_BALANCE NUMBER(15,2) DEFAULT 0 NOT NULL,
DEPOSIT_AMOUNT NUMBER(15,2) DEFAULT 0 )


Thanks,
Sameer

 

[Thargy]

sameer,

the fastest way that I can think of to do this is as follows.

Create table_A, as per your last posting.
Then run

INSERT INTO TABLE_A
  (SELECT SYSDATE, 'accountid', 'cust','asset_id', 'vault',LEVEL+1, LEVEL
     FROM dual CONNECT BY LEVEL < 1000001);

This will insert 1 million rows into table_A.
I believe that it's the fastest possible method, since selecting from dual involves on I/O. The only restriction is how fast your hard disks can write that volume of data.

Regards

Tharg

Grinding away at things Oracular