SQL Insert Into
The SQL INSERT INTO Statement
To add new records to a table, use the INSERT INTO statement.
INSERT INTO Syntax
Two potential formats for the INSERT INTO statement are as follows:
- Indicate the columns to insert the values into as well as their names:
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
- You do not need to provide the column names in the SQL query if you are adding data to every column in the table. Make sure, though, that the values are arranged in the same order as the table’s columns. In this case, the INSERT INTO syntax would look like this:
INSERT INTO table_name
VALUES (value1, value2, value3, …);
Demo Database
| CustomerID | CustomerName | ContactName | 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 |
INSERT INTO Example
A new record is inserted into the “Customers” database using the SQL statement that follows:
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
The selection from the “Customers” table will now look like this:
| 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 | Tom B. Erichsen | Skagen 21 | Stavanger | 4006 | Norway |
Insert Data Only in Specified Columns
You may also choose which columns to insert data into.
The new record that is created by the SQL statement that follows will only have data entered into the “CustomerName,” “City,” and “Country” columns (the CustomerID will be updated automatically).
Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
This is how the “Customers” table selection should now appear:
| 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 |
Insert Multiple Rows
It is also feasible to add more than one row to a single statement.
The same INSERT INTO statement is used, but with different values, to insert numerous rows of data:
Example
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');
Make sure to use a comma (,) to separate each pair of values.
This is how the “Customers” table selection should now appear:
| 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 | Tom B. Erichsen | Skagen 21 | Stavanger | 4006 | Norway |
| 93 | Greasy Burger | Per Olsen | Gateveien 15 | Sandnes | 4306 | Norway |
| 94 | Tasty Tee | Finn Egan | Streetroad 19B | Liverpool | L1 0AA | UK |
