Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
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