Inserting Data - MariaDB Crash Course - page 163

Note
Instructions Needed for the Next Example The following example imports data from
a table named custnew into the customers table. To try this example, create a
new table named custnew using the CREATE TABLE customers statement in
create.sql , and obviously replacing customers with custnew . Then add a few
customers of your own, being careful to not use cust_id values that were already
used in customers (the subsequent INSERT operation will fail if primary key values
are duplicated). The easiest way to do this is just start the numbers much higher,
perhaps at 20000 .
▼ Input
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
▼ Analysis
This example uses INSERT SELECT to import all the data from custnew into
customers . Instead of listing the VALUES to be inserted, the SELECT state-
ment retrieves them from custnew . Each column in the SELECT corresponds
to a column in the specified columns list. How many rows will this statement
insert? That depends on how many rows are in the custnew table. If the table
is empty, no rows are inserted (and no error is generated because the operation
is still valid). If the table does, in fact, contain data, all that data is inserted into
customers .
This example imports cust_id (and assumes that you have ensured that
cust_id values are not duplicated). You could also simply omit that column
(from both the INSERT and the SELECT ) so MariaDB would generate new
values.