Re: Repetitive insert statements

CH. wrote:
> Hi,
>
> I'm working on a database assignment and ran into a problem. I have a
> table and I need to insert 50 records into it. Right now I have 50
> insert statements that enter all of the data correctly but I just
> found out that I cannot do it that way. I'm suppose to have just 1
> insert command and execute run to enter each value (What a great way
> to insert data!). Please look below for an example.
>
>
> create table tb(id char(4), col1 char(5), col char(5));
>
> SQL> insert into tb values (&id, &col1, &col2);
> Enter value for id: 1234
> Enter value for col1: 12345
> Enter value for col2: 78905
> old 1: insert into tb values (&id, &col1, &col2)
> new 1: insert into tb values (1234, 12345, 78905)
> SQL> run
> 1* insert into tb values (&id, &col1, &col2)
> Enter value for id: 2345
> Enter value for col1: 13241
> Enter value for col2: 43211
> old 1: insert into tb values (&id, &col1, &col2)
> new 1: insert into tb values (2345, 13241, 43211)
> SQL>
> SQL> run
> 1* insert into tb values (&id, &col1, &col2)
> Enter value for id: 7894
> Enter value for col1: 74125
> Enter value for col2: 96325
> old 1: insert into tb values (&id, &col1, &col2)
> new 1: insert into tb values (7894, 74125, 96325)
> SQL>
>
> Is there a way to put everything into a script? I'd appreciate any
> hint or pointer or example. Please help.
>
> Thank you
> Chatt H.

Does it have to be run from sqlplus? Can you use sql loader to load a comma-separated file of values? Can you use a GUI tool that lets you type values directly in cells? Can you use a jsp form that lets you submit values?

Notes:
* Instead of typing 'run', you could have typed '/' (which means run the statement in the current buffer).
* You conveniently supplied numbers for each of the columns when prompted. What would have happened if you typed letters? I think you need to make your insert statement look like this:

insert into tb values ('&id', '&col1', '&col2'); Received on Tue Nov 19 2002 - 11:58:58 CST