sql script to insert multiple rows

[Chassidy Rentz]

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 = ' '

[TwoProc · Accepted solution]

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

[Chassidy Rentz]

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.

[TwoProc]

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');

[TwoProc]

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

[Chassidy Rentz]

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

[Chassidy Rentz]

I changed one thing and it worked!

THANKS!

[TwoProc]

Great! Glad to hear it all worked.