topic Re: script to fill up the table in Operating System - HP-UX

script to fill up the table

Deanna Tran — Mon, 10 Jun 2002 13:40:43 GMT

I'd like to know if anyone has any simple script to fill up the table? And once it is fill, I would like to be able to perform transaction to it as well. I know we have to use ( either commit, savepoint or rollback) but i don't know the syntax on how to do so?
thnaks for your help

Re: script to fill up the table

Rodney Hills — Mon, 10 Jun 2002 13:46:32 GMT

Not sure what database your are refering or table, but I have loaded up a table from a comma delimited file by running a short perl program to generate insert commands-

open(INP,"while() {
chop;
@a=split(/,/,$_);
print "INSERT ('",join("','",@a),"' INTO MYTABLE\n";
}

Then feed the resultant file to your database system.

-- Rod Hills

Re: script to fill up the table

Ralph Grothe — Mon, 10 Jun 2002 14:52:22 GMT

Hi Deanna,

not sure what table you refer to,
I hope not your OS's process table ;-)

If you really mean an RDBM's data table, and you are into Perl, I would first get the DBI module from CPAN, install this, and then the appropriate low level DBI driver for your specific DBMS
(if it were Oracle, you'd go for DBD::Oracle)
This is much better than the suggested here file stuff.
Please, have a look at

http://www.perldoc.com/perl5.6.1/lib/DBI/FAQ.html
http://www.saturn5.com/~jwb/dbi-examples.html
http://www.cpan.org/modules/by-module/DBI/
http://www.cpan.org/modules/by-module/DBD/

Re: script to fill up the table

Brian Crabtree — Mon, 10 Jun 2002 23:59:29 GMT

Deanna,

Can you give an example of what you are trying to do? I might be able to give you some pointers on ways to do it from a sqlscript, or shell script.

Thanks,

Brian

Re: script to fill up the table

Victor Geere — Tue, 11 Jun 2002 07:36:19 GMT

insert into tablename(field1,field2) values(1,2);

insert into tablename
select * from tablename;

run the second statement for a couple of times and you will end up with 2,4,8,16,32,64,128,256,512,1024,2048 records after each run.

Re: script to fill up the table

Deanna Tran — Tue, 11 Jun 2002 13:57:05 GMT

hi here is the script that I wrote, but when i
run it it gives me an error.. I want it to load
indefinetely, and control C to exit...if I control C to exit, then it should able to continue loading the data...

declare
select max(empid) from employee;
numcount number:= max(empid);
begin
while true loop
numcount:=numcount+1;
insert into employee values (numcount,'dfkjebkg','dlkjglkj',5678.34,4567895);
commit;
end loop;
end;

Re: script to fill up the table

Volker Borowski — Tue, 11 Jun 2002 14:11:37 GMT

Hi,

declare empid as a SEQUENCE and skip it in the insert statement, just loop and insert data, oracle will take care of the rest.

Hope this helps
Volker

Re: script to fill up the table

Jeanine Kone — Tue, 11 Jun 2002 14:32:23 GMT

This will work:

PROCEDURE tester1 IS
numcount number:= 0;

begin
while true loop
numcount:=numcount+1;
insert into employee values (numcount,'dfkjebkg','dlkjglkj',5678.34,4567895);
commit;
end loop;

END;

I ran this from sql*plus (i.e. execute tester1) and it inserted data until I hit my maxextents (which I set small just incase I could not kill it quickly enough).

Re: script to fill up the table

Deanna Tran — Tue, 11 Jun 2002 14:48:14 GMT

hi jeanine,
I tried your suggestion, but i got this error:
PROCEDURE tester1 IS
*
ERROR at line 1:
ORA-00900: invalid SQL statement..
and yes, i did login as test1

Re: script to fill up the table

Jeanine Kone — Tue, 11 Jun 2002 14:57:06 GMT

That is the actual code of the procedure. To create the procedure, just add a create before the command. Also, make sure the user you are logged in as has permissions to create stored procedures.

i.e.
create PROCEDURE tester1 IS
numcount number:= 0;
begin
while true loop
numcount:=numcount+1;
insert into employee values (numcount,'dfkjebkg','dlkjglkj',5678.34,4567895);
commit;
end loop;
END;

Re: script to fill up the table

Deanna Tran — Tue, 11 Jun 2002 17:26:14 GMT

hi jeanine,
I am able to create the procedure, but
how can i monitor what it is doing ? is there a way i monitor it...

Re: script to fill up the table

Jeanine Kone — Tue, 11 Jun 2002 18:36:41 GMT

The simple way to check on it would be to open a second sql*plus session and do a "select count(*) from employee;". This will show you how many rows are in the table. You can do this over and over and watch the table growing.

Re: script to fill up the table

Deanna Tran — Tue, 11 Jun 2002 19:14:38 GMT

hi,
how long would it takes the table to grow, when i do select count(*) from employee;
it gives me a value that is differ when
i do select max(empid) from employee...
in addition, when i run that script, it comes
back as "procedure created"
> @/u02/dtloader2.sql

Procedure created.

SQL>

Re: script to fill up the table

Jeanine Kone — Tue, 11 Jun 2002 19:42:11 GMT

OK - The script creates a procedure that will insert data. You need to actually execute the procedure after it is created. From sql*plus, you type "execute tester1". The procedure will then execute and start inserting the data.

Once you are inserting the data, it will grow very quickly. If you want the count(*) to equal the emplid (assuming the numcount value is being inserted in the emplid field)- you should delete any data that is currently in the table before executing the script (i.e. "truncate table employee;").

If you do the queries while the script is running they will not match up (because it will continue to insert data during your queries, so the results will not be consistent). But once you stop the procedure - they should match.