SQL Insert Into Statement

SQL Insert Into Statement

This is a detailed guide on SQL Insert Into Statement. Learn how to add new database table records using the Insert Statement in SQL with examples.

Use of SQL Insert Into Statement

As clear by the name itself, the SQL Insert Into Statement is used to insert new rows or records into the database tables. Using this statement one can insert one or more rows into the database table using a single query. It also provides the flexibility of inserting the values for only selected columns as well.

Insert Into Syntax

SQL Insert Into Statement can be used in three different ways, so there are three different syntaxes given below.

In the above SQL Insert query, we’re inserting the values val_1, val_2, and val_3 respectively into the columns col_1, col_2 and col_3 of the database table named table_name.

There’s another syntax that can be written without the need of mentioning the column names. You can simply write the values in the same order as the columns actually structure in the database table. But make sure the order is right, otherwise, you’ll end up inserting the wrong values in the wrong columns or may face errors.

Note. For the above syntax, you should insert values for all columns. Therefore, the number of values should be exactly equal to the number of columns. Even the auto-increment columns should be included. You can set the value null for auto-increment columns for not affecting any sequence order. You’ll find more about this in the Example section.

The INSERT INTO statement also allows you to insert multiple rows at once. All you need to do is to repeat the statement in the parenthesis as many times as the number of records to be inserted. The syntax is written below.

Again, you may or may not mention the column names in this query as well depending upon the order.

Insert Into Examples

There are four different examples given below. In each of the examples, we’re using the SQL Insert Into statement in a different way. In our demo database, we’ll run the queries on the books table. Have a look at the existing table data in the following screenshot.

SQL Insert Into Examples

There are currently only 5 rows, we’ll insert more rows into this table using the SQL Insert Into Queries illustrated in the examples.

Insert Into All Columns

The following query inserts a new row into the table with all the required values. But we’re not inserting the value for the ID column because it is auto-increment and primary key for the table and it will update itself automatically whenever a new record is inserted.

Single Row All Columns Value Inserted Example

You can see in the output success message that our new record has been inserted with row id 11. The screenshot of the table contents after executing all the example queries is provided at the end of this section.

Inserting Values In Columns Order

Note. As the first column of the table ID is auto-incrementing, I’ve set the value for it as null. This will auto-increment the sequence and will assign the expected value for the ID column as per the ongoing sequence automatically.

SQL Insert Into Statement Example

Insert Into Specific Columns

The following query will only insert the values for the columns book_name and book_authors, leaving the values of all other columns as null.

SQL Insert Into Select Columns Example

The query will be executed successfully, but you can see in the above output screenshot that it is giving a few warnings regarding not having a default value for the other columns. This is because as per the table definition, I’ve not set any default values for the columns if a value for the columns is not provided in the SQL Insert Into statement.

So, in my case, this left the column values blank but not null. If you might have defined the table column values can be null, then it might have set the column values to null for which columns the values are not provided.

Note. The empty column does not mean Null. Read more about it here.

Inserting Multiple Rows

In our last query given below, we’re inserting multiple records at the same time.

SQL Insert Into Multiple Rows Example

Now you can see in the following screenshot after the execution all the above four queries the table now contains more data.

SQL Table With More Rows Inserted

I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.

Helpful Associated Guides.

Also, don’t forget to Subscribe to WTMatter!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

Your email address will not be published. Required fields are marked *