This article contains basic SQL INSERT statements that beginners can use to insert data into their database tables.

Basic Syntax

The basic syntax for inserting data in SQL goes like this:

INSERT INTO TableName (Column1, Column2, Column3, ...)
VALUES (Value1, Value2, Value3, ...);

Where:

• TableName is the name of the table you want to insert data into
• Column1, etc are the names of the columns you want to insert data into
• Value1, etc are the values that you are inserting into those columns. The order of these values must match the order that you listed the columns (i.e. Value1 goes into Column1, etc).

You can omit the column names if you’re inserting values into all columns. In that case, you can use the following syntax:

INSERT INTO TableName
VALUES (Value1, Value2, Value3, ...);

Note that some DBMSs use a more complex syntax than this (i.e. they offer more options), but this is generally the basic syntax required to insert data into a table.

Insert into All Columns

Here’s a basic example that inserts data into all columns in the table:

INSERT INTO PetTypes
VALUES( 1, 'Bird' );

In this case, there are two columns in the PetTypes table, and we’re inserting values into both columns, therefore, we don’t need to specify the column names.

The above statement is the same as doing this:

INSERT INTO PetTypes( PetTypeId, PetType )
VALUES( 1, 'Bird' );

We can check our data by running a SELECT statement.

SELECT * FROM PetTypes;

Result:

+-------------+-----------+
| PetTypeId   | PetType   |
|-------------+-----------|
| 1           | Bird      |
+-------------+-----------+

Insert into Some Columns

Here’s a basic example that inserts data into just some of the columns in the table:

INSERT INTO Users( UserId, FirstName, LastName )
VALUES( 1, 'Lansell', 'Letterman' );

This table actually has four columns, but we only insert data into three of those columns. We can see that when we run a SELECT statement against the table after inserting the data.

SELECT * FROM Users;

Result:

+----------+-------------+------------+-----------------------------+
| UserId   | FirstName   | LastName   | DateInserted                |
|----------+-------------+------------+-----------------------------|
| 1        | Lansell     | Letterman  | 2020-11-21 05:18:50.0029455 |
+----------+-------------+------------+-----------------------------+

How to Insert Values in the Wrong Order

The column list method enables you to insert data that isn’t in the same order:

INSERT INTO Users( LastName, FirstName, UserId )
VALUES( 'Pitt', 'Bart', 2 );

In this example I’ve rearranged the order of the columns and their respective values. Fortunately, the column list provides the DBMS with guidance as to where each value should be inserted.

So now, if we select our data, we will see that it has been inserted into the correct columns.

SELECT * FROM Users;

Result:

+----------+-------------+------------+-----------------------------+
| UserId   | FirstName   | LastName   | DateInserted                |
|----------+-------------+------------+-----------------------------|
| 1        | Lansell     | Letterman  | 2020-11-21 05:18:50.0029455 |
| 2        | Bart        | Pitt       | 2020-11-21 05:30:07.4456867 |
+----------+-------------+------------+-----------------------------+

Insert the Result of a Query

It’s also possible to insert the result set of a query into a table. There are a couple of approaches you can take with this.

• Use the INSERT INTO... SELECT statement. In this case, you need to create the table first, before inserting a query result set into it.
• Use the CREATE TABLE … AS SELECT Statement. This method automatically creates a new table based on the result set of the query, then inserts those results into the table. Although this method conforms to the SQL standard, it is not supported by all DBMSs.
• Use the SELECT INTO statement. This is similar to the previous option. It automatically creates a new table based on the result set of the query, then inserts those results into the table. If the previous method (CREATE TABLE ... AS SELECT) isn’t supported by your DBMS, try this method.