SQL: How to use a SELECT statement and VALUE statement at the same time within INSERT?

Question

I would like to use SERIAL as the ID for a newly created table but populate it from another table. My table looks like this:

CREATE TABLE dimCustomer3
(
    customer_key SERIAL PRIMARY KEY,
    customer_id  smallint NOT NULL,
    first_name   varchar(45) NOT NULL,
    last_name    varchar(45) NOT NULL,
    email  varchar(45) NOT NULL
);

I can insert data into this by using a VALUES statement like this:

VALUES(DEFAULT, 1, 'Joe', 'Smith', 'jsmith@email.com');

However, I only want to keep the DEFAULT from this one but populate the rest from an existing table. I tried something like this:

INSERT INTO dimCustomer3 (customer_key, customer_id, first_name, last_name, email) 
VALUES(DEFAULT) 
CROSS JOIN (SELECT c.customer_ID, c.first_name, c.last_name, c.email FROM customer c) a

and like this:

INSERT INTO dimCustomer3 (customer_key, customer_id, first_name, last_name, email) 
VALUES(DEFAULT, (SELECT c.customer_ID, c.first_name, c.last_name, c.email FROM customer c));

but none of them worked.

Answer 1

Simply omit the column in the insert-select and in most database tables the action will insert default values where specified for those columns.

Though in most auto-increment, identity, or serial columns that auto-generate values, you should never directly insert into such columns.

INSERT INTO dimCustomer3 (customer_id, first_name, last_name, email) 
SELECT c.customer_ID, c.first_name, c.last_name, c.email 
FROM customer c;