topic Re: sql script to insert multiple rows in Operating System - HP-UX

sql script to insert multiple rows

Chassidy Rentz — Wed, 13 Jul 2005 12:31:26 GMT

I need help writing a SQL script that will insert multiple rows into a table (A) with a 4-column primary key. I need to select data for one of the columns from another table (B).

Table A is called STATUS
Table B is called I2P_TCN

The primary key columns for STATUS are TCN, ACTION_TYPE, ACTUAL_ACTION_DATE, ACTUAL_ACTION_TIME

I need to select the TCN's to be inserted from the I2P_TCN table where PICKUP_RECORD_NBR = ' '

Re: sql script to insert multiple rows

TwoProc — Wed, 13 Jul 2005 12:39:15 GMT

insert into status (
select tcn,'MYACTION',SYSDATE,SYSDATE from I2P_TCN where pickup_record_NBR = '12');

Re: sql script to insert multiple rows

Chassidy Rentz — Wed, 13 Jul 2005 12:41:23 GMT

Only the one column, TCN, is found in Table B. I would like to insert my own values for the other 3 columns in table A. All I need from table B are the TCN values.

Re: sql script to insert multiple rows

TwoProc — Wed, 13 Jul 2005 12:43:59 GMT

That's what I gave you. Notice that the rest is a) a literal 'MYACTION', and the current time (SYSDATE). Only tcn is coming from the I2P_TCN table. Feel free to put in what ever value you wish for the other values, much like what I did in my example.

insert into status (
select tcn,'MYACTION',SYSDATE,SYSDATE from I2P_TCN where pickup_record_NBR = '12');

Re: sql script to insert multiple rows

TwoProc — Wed, 13 Jul 2005 12:45:27 GMT

For clarification - SYSDATE is a keyword that returns the current date and time into a date field of a statement.

Re: sql script to insert multiple rows

Chassidy Rentz — Wed, 13 Jul 2005 12:48:56 GMT

when I do this I get the following error:

SQL> insert into status (select tcn, 'CO', '12-JUL-05','16:51' from
2 i2p_tcn where pickup_record_nbr like 'M800%');
insert into status (select tcn, 'CO', '12-JUL-05','16:51' from
*
ERROR at line 1:
ORA-00947: not enough values

Re: sql script to insert multiple rows

Chassidy Rentz — Wed, 13 Jul 2005 12:54:15 GMT

I changed one thing and it worked!

THANKS!

Re: sql script to insert multiple rows

TwoProc — Wed, 13 Jul 2005 13:54:19 GMT

Great! Glad to hear it all worked.