cancel
Showing results for 
Search instead for 
Did you mean: 

script to fill up the table

SOLVED
Go to solution
Deanna Tran
Frequent Advisor

script to fill up the table

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
13 REPLIES
Rodney Hills
Honored Contributor

Re: script to fill up the table

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
There be dragons...
Ralph Grothe
Honored Contributor

Re: script to fill up the table

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/
Madness, thy name is system administration
Brian Crabtree
Honored Contributor

Re: script to fill up the table

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
Frequent Advisor

Re: script to fill up the table

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.
I thought that I was because I had thought.
Deanna Tran
Frequent Advisor

Re: script to fill up the table

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
Honored Contributor

Re: script to fill up the table

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
Trusted Contributor

Re: script to fill up the table

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
Frequent Advisor

Re: script to fill up the table

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
Trusted Contributor

Re: script to fill up the table

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
Frequent Advisor

Re: script to fill up the table

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
Trusted Contributor
Solution

Re: script to fill up the table

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
Frequent Advisor

Re: script to fill up the table

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
Trusted Contributor

Re: script to fill up the table

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.