5 – SQL Insert data

In this guide, you will learn the syntax and see some practical examples of how to use the SQL insert data command. We will introduce some new Keywords including INSERT INTO and look at how you can insert a single or multiple rows.

SQL Insert data table of contents

Learn SQL online

For our instructor lead, more in depth look at learning to insert data why not check out our video course hosted on UDEMY or enjoy the free SQL course below.
SQL Insert Data syntax

Let’s start by understanding the basic concepts and syntax involved. SQL uses the INSERT INTO statement to add new data to a table. Within the insert statements we need to specify several elements including:

Table Name: Specify the name of the table into which you want to insert data.

Column Names: If you’re not inserting data into a table in the same order and utilising all the columns, you must specify the names of the columns you want to populate.

Values: Provide the actual data values you want to insert into the specified columns. The order of the values should correspond to the order of the columns.

The basic syntax for an SQL INSERT statement looks like this:

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

We start by using the Keywords INSERT INTO then specify the table name that we want to insert data into. Next, we add a list of columns that need to have data inserted. They are separated by a comma and encapsulated in open and closed brackets. Finally the keyword VALUES is used where the data is specified that needs to be inserted into the table. The values must line up with the specified columns. This statement will insert a single row. 

SQL insert examples

Let’s illustrate the theory with some practical examples using a fictional database for an online bookstore. We have a table called books with columns: book_id, title, author, and price.

				
					INSERT INTO books (title, author, price)
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 9.99);

				
			

This SQL statement inserts a new book record into the books table, providing values for the title, author, and price columns. Notice how each of the values corresponds to the specified field.  Also, notice that we use single quotes to insert textual data into character fields.

Inserting Multiple Records

You can also insert multiple records in a single SQL statement using a comma-separated list of values:

				
					INSERT INTO books (title, author, price)
VALUES 
  ('To Kill a Mockingbird', 'Harper Lee', 12.99),
  ('1984', 'George Orwell', 10.49),
  ('The Catcher in the Rye', 'J.D. Salinger', 11.25);

				
			

This inserts three new book records into the books table. You should note that there is a limit on the number of rows that you can insert depending on which database use use. For SQL server this is 10,000, so if you want to insert more than that then you need to batch the inserts or use a bulk copy.

Inserting Data into All Columns

If you want to insert data into all columns of a table, you can omit the column names and provide values for all columns in the same order as they appear in the table:

				
					INSERT INTO books
VALUES (4, 'The Hobbit', 'J.R.R. Tolkien', 14.95);

				
			

This example inserts a new book record into the books table, providing values for all columns. It is generally best practice to specify the columns that you want to insert data into.

Conclusion

Inserting data into an SQL database is a fundamental operation that is crucial for managing your database records. With a solid understanding of the theory and syntax, you can easily add new data to your tables. Whether you are building applications, maintaining databases, or conducting data analysis, the ability to insert data is a fundamental skill in the world of data management. So, make sure you practice and understand the SQL INSERT INTO statement, and you’ll be well on your way to effectively managing your database records.

Please see the other SQL learning articles below as part of this learning SQL online course. 

If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY. 

Learning SQL programming with SQL server
Learn SQL Online

Free online SQL course sections