script to fill up the table

[Deanna Tran]

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

[Rodney Hills]

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

[Ralph Grothe]

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/

[Brian Crabtree]

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

[Victor Geere]

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.

[Deanna Tran]

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;

[Volker Borowski]

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

[Jeanine Kone]

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).

[Deanna Tran]

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

[Jeanine Kone]

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;

[Deanna Tran]

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...

[Jeanine Kone · Accepted solution]

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.

[Deanna Tran]

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>

[Jeanine Kone]

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.