Search code examples
mysqlsqlinsert-select

MySQL: Is there a way to insert values from one table to another?

I created a table called employee

CREATE TABLE employee(
     id INT,
     name VARCHAR(50),
     credit_card_number VARCHAR(20),
     expr_date CHAR(6),
     PRIMARY KEY(id)
)

And then I have a table that stores the credit cards information

CREATE TABLE credit_card (
     credit_card_number VARCHAR(20),
     expr_date CHAR(6),
     o_datetime DATETIME
)

I want to write an insert statement that inserts the currently stored credit card information for every employee into my new credit card table. i also want to fill in my o_datetime with the current date time.

This is how i approached it initially, but i'm not exactly sure if i'm doing this right..

INSERT INTO credit_card(credit_card_number, expr_date, CURRENT_TIMESTAMP AS o_datetime)
SELECT credit_card_number, expr_date
FROM employees;

But i get an error when I run this. I'm really new to SQL so I might be missing a simple step, but I can't seem to figure it out.

Solution

  • It should look like this:

    INSERT INTO credit_card(credit_card_number, expr_date, o_datetime)
SELECT credit_card_number, expr_date, CURRENT_TIMESTAMP
FROM employee;

    You need to define the 3rd column of your insert, in this case, CURRENT_TIMESTAMP for o_datetime.