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;