SQL INSERT INTO

The SQL INSERT INTO Statement

The INSERT INTO statement facilitates the addition of new records into a table.

INSERT INTO Syntax

You can write the INSERT INTO statement in two ways:

1. Provide both the column names and the corresponding values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

2. If values for all columns of the table are being added, specifying column names in the SQL query is unnecessary. However, ensure that the values are arranged in the same order as the columns in the table. The INSERT INTO syntax in this case would be:

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

Demo Database

Here is an excerpt from the Customers table that has been used in the examples:

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

INSERT INTO Example

The subsequent SQL statement inserts a new record into the “Customers” table:

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’, ‘Tom B. Erichsen’, ‘Skagen 21’, ‘Stavanger’, ‘4006’, ‘Norway’);

The excerpt from the “Customers” table will appear as follows now:

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

Did you observe that we haven’t included any specific number for the CustomerID field?

The CustomerID column is configured as an auto-increment field, meaning it will be generated automatically when a new record is inserted into the table.

Insert Data Only in Specified Columns

You can also choose to insert data into specific columns only.

The subsequent SQL statement will add a new record, but data will be inserted only into the “CustomerName”, “City”, and “Country” columns (CustomerID will be updated automatically):

Example

INSERT INTO Customers (CustomerName, City, Country)
VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);

The excerpt from the “Customers” table will now appear as follows:

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 possible to insert multiple rows in one statement.

To insert multiple rows of data, we use the same INSERT INTO statement, but with multiple values:

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’);

Ensure each set of values is separated by a comma ,.

The excerpt from the “Customers” table will now appear as follows:

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