Table Insert

Insert Into New

certain columns

If you are adding values to specific columns in a table
Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)

omit columns

NOTE:

If you insert into 3 columns and the table contains more than 3
If those additional columns are not to contain an auto-increment fields, then NULL will be inserted into the columns that were not listed in the INSERT INTO statement
Here is an example

# -- Here we only wish to insert into 3 columns - exlucing CustomerID which is an auto-increment field
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

As you see in the output below, the columns omitted from the INSERT INTO statement contain null

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
89	White Clover Markets	Karl Jablonski	305 - 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland
92	Cardinal	null	null	Stavanger	null	Norway

all columns

If your adding values to all the columns in the table you do not need to specify the column names
Make sure the order of the values is in the same order as the columns
Syntax:

INSERT INTO table_name 
VALUES (*value1*, *value2*, *value3*, ...)

auto-increment

Most tables will have an auto-increment field, for IDs or other values, so there is no need to insert into that specific column
Once you insert a record for the other columns the auto-increment column will self-generate

multiple rows

Just as we did above for one row you can just list all the rows one after the other like this:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

Insert Into Select

SQL does not include a function for importing data. Instead, a method you can use to import data from one table to another is to use the INSERT INTO command together with a SELECT statement.

Here we just want to copy columns from one table to another, since SQL does not provide a copy function we use
INSERT INTO SELECT just as we did before but this time we specify the table we want to copy from in WHERE
INSERT INTO SELECT copies data from one table and inserts it into another table
INSERT INTO SELECT requires that the data types in source and target tables match
The existing records in the target table are not affected
Syntax:

INSERT INTO     [destination_table_name]
SELECT          [column names, separated by commas, or * for all columns]
FROM            [source_table_name]
WHERE           [condition]

Example:

Here we are copying all the columns from customers
Into customer_promotion
We happen to have a filtering WHERE statement as well

INSERT INTO     customer_promotion 
SELECT          * 
FROM            customers 
WHERE           total_sales = 0 AND postal_code = '12345'

certain columns

The statement above can be used to copy * all certain columns into another table
Here we’ll show how to copy certain columns
Syntax:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ... 
FROM table1 
WHERE condition