Fill in the NOT NULL columns of a table while using the INSERT INTO statement

Question

I'm working through a problem where I need to insert 135 rows into a recently created table with a select statement. I have a handful of NOT NULL constraints on that table and I don't understand how to alter my SELECT to insert the correct information.

Here's what I'm trying to do:

CREATE SEQUENCE target_table_s1 START WITH 1001;

INSERT INTO target_table(colA,ColB,ColC,ColD,ColE)
target_table_s1.NEXTVAL,
(SELECT (colB,colC,ColD)
FROM source_table),
colE;

Where colA is a sequence number (to provide a primary key for the target_table) and colE basically just needs to be something simple like SYSDATE.

Any suggestions on how I can make this work? I know that what I've written above isn't going to work but it's the best way I can illustrate what I'm trying to accomplish. Do I need to find a way to put my sequence inside the select statement so it follows the proper "INSERT INTO SELECT" format?

Tim Biegeleisen · Accepted Answer · 2016-10-17 21:07:14Z

4

I think you should just be using the INSERT INTO ... SELECT construct here:

INSERT INTO target_table (colA,ColB,ColC,ColD,ColE)
SELECT target_table_s1.NEXTVAL, ColB, ColC, ColD, SYSDATE
FROM source_table

I assume above that you want to insert the SYSDATE into column E.

answered Oct 17, 2016 at 21:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

theonlydidymus Over a year ago

So this is pretty much what I'm looking for and I've almost solved my problem. Maybe you can help me with the last little bit. Here's my new context:

theonlydidymus Over a year ago

INSERT INTO target_table(colA, colB, colC, colD, colE)

theonlydidymus Over a year ago

SELECT seq.NEXTVAL, f.colB, f.colC, f.colD, SYSDATE FROM (SELECT colB, colC, colD FROM source_table); How do I alias the source table as f? Do I just use "FROM source_table f"? What if the from statement involves multiple tables? (sorry, my comments keep submitting even when I haven't finished)

Tim Biegeleisen Over a year ago

Yes, if you want to alias source_table then use FROM source_table AS f

theonlydidymus Over a year ago

This worked perfectly in my practical code. Accepted answer.

Collectives™ on Stack Overflow

Fill in the NOT NULL columns of a table while using the INSERT INTO statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related