Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

sql script to insert multiple rows

SOLVED
Go to solution
Chassidy Rentz
Contributor

sql script to insert multiple rows

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 = ' '
7 REPLIES
TwoProc
Honored Contributor
Solution

Re: sql script to insert multiple rows

insert into status (
select tcn,'MYACTION',SYSDATE,SYSDATE from I2P_TCN where pickup_record_NBR = '12');
We are the people our parents warned us about --Jimmy Buffett
Chassidy Rentz
Contributor

Re: sql script to insert multiple rows

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

Re: sql script to insert multiple rows

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');
We are the people our parents warned us about --Jimmy Buffett
TwoProc
Honored Contributor

Re: sql script to insert multiple rows

For clarification - SYSDATE is a keyword that returns the current date and time into a date field of a statement.
We are the people our parents warned us about --Jimmy Buffett
Chassidy Rentz
Contributor

Re: sql script to insert multiple rows

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
Contributor

Re: sql script to insert multiple rows

I changed one thing and it worked!

THANKS!
TwoProc
Honored Contributor

Re: sql script to insert multiple rows

Great! Glad to hear it all worked.
We are the people our parents warned us about --Jimmy Buffett