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

Question

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.

"But i get an error when I run this" --- do we need to guess it? But it is obviously the number of columns. You're trying to insert 3 columns and select only 2. — zerkms, Mar 08 '15 at 21:00

score 3 · Answer 1 · answered Mar 08 '15 at 21:07

First, you should never store unencrypted credit card numbers in a database. This is an invitation for someone to "borrow" the numbers. You can hash them or store them in some other fashion to prevent unauthorized access.

The problem with your statement is the o_datetime component. The default value can go in the select statement:

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

However, if you always want this to be the date that the data was inserted, you can just make it the default value:

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

Then you can do:

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

Note that in older versions of MySQL, o_datetime would need to be a timestamp.

"you should never store unencrypted credit card numbers in a database" --- the general advice is to never store them at all, as it "violates" PCI (quotes because it's not but if anyone asks such a question - then they definitely do not match its requirements). — zerkms, Mar 08 '15 at 21:11
Yeah. What company do you work for? I don't want to buy anything from you. — Rick James, Mar 08 '15 at 21:35

score 1 · Accepted Answer · answered Mar 08 '15 at 21:01

1

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.

SQL Fiddle Demo

answered Mar 08 '15 at 21:01

sgeddes

62,311
6
61
83

i tried running this, but I get an error saying that my Table 'credit_card' already exists. weird.. – Kara Mar 08 '15 at 21:02
@Kara -- works for me, look at the posted fiddle and let me know what's wrong... BTW -- you're creating a `employee` table but calling `employees` in your `insert` statement. – sgeddes Mar 08 '15 at 21:06

score 1 · Answer 3 · answered Mar 08 '15 at 21:03

Your syntax is incorrect. The list after the into clause can only contain column names. All the values (even if they are constants or function return values) must be given in the select list:

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

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

3 Answers3