In the previous article, we’ve created two tables, and now we’re ready to use the SQL INSERT INTO TABLE command and populate these tables with data. In order to do so, we’ll prepare statements in Excel and then paste these statements into SQL Server and execute them. We’ll also check the contents of both tables before and after these commands using the SELECT statement. So, let’s start.

INSERT INTO TABLE statement

There is no point in creating database structures and not having any data in the database. The INSERT INTO TABLE statement is the one we’ll use to solve this problem. It is one of the four important SQL DML (Data Manipulation Language) statements SELECT … FROM, INSERT INTO TABLE, UPDATE … SET, and DELETE FROM… The complete syntax is pretty complex since the INSERT INTO could also be a very complex statement. Please take a look at the T-SQL INSERT INTO TABLE complete syntax here.

In this article we’ll use simplified, but also most common syntax:

INSERT INTO table_name (column_list) VALUES (column_values);

In such INSERT INTO statement, you’ll need to define the table_name where you’re inserting data into, list all columns (maybe you’ll use all of them, but maybe only a few of them), and then list all values. Notice that column values should match column types definition (e.g., you can’t insert a textual value into the numerical column/attribute).

In case you’re inserting all values in the table, you don’t need to list all columns after the table_name and you could use even more simplified syntax:

INSERT INTO table_name VALUES (column_values);

I personally prefer listing all column names, because this approach would work even if we add new columns to the existing table.

• Note: The INSERT INTO TABLE statement could be written in such manner we insert multiple rows with 1 statement or even combined with the SELECT statement.

The simplified syntax for one such case where INSERT and SELECT statements are combined is given below:

INSERT INTO TABLE example

Before doing anything, let’s check what is stored in our tables. This is the model we have created in the previous article. You can see that we have one table where we’ll store data related to countries and another one for data related to cities. They are also related to each other, but we’ll talk about that in the following article:

In order to check the contents of these two tables, we’ll use two simple SELECT statements:

While SELECT is not the topic of this article, it should be mentioned that its’ basic syntax is:

SELECT 1 or more attributes FROM table;

The star (*) after SELECT represents that we want to show the values of all attributes/columns from that table in the query result.

As expected, there is nothing in these two tables, and SQL Server returns the result, as shown in the picture below. Statements return names of the columns from the tables we used in the SELECT query, but there is nothing under these column names. You can look at this as an empty Excel sheet with defined column names (headers). You know what type of data should be there, but there is nothing:

Now, we’ll need to change that.

First, we’ll populate the country table using the following INSERT INTO TABLE statements:

Data for five countries were successfully inserted. The result is shown in the picture below. Since we had 5 INSERT INTO TABLE statements we have 1 “(1 row affected)” message for each of these five commands in the “Messages” section:

Please note that all values (after VALUES) were ordered in the same manner in which we listed columns (after INSERT INTO country). All three values are texts. The query would work even if we haven’t ordered them in the right manner because all of them have the same data type (text), but the data would be stored in the wrong columns. In that case, we would have a semantic error.

The next thing we need to do is to populate the city table. We’ll do that using the following statements:

After executing these statements, this was the result. As expected, 6 rows were added. And once more we have 1 message for each insert in the Messages section:

In this case, we would have a problem if we haven’t listed values in the same manner, we listed columns because their data types are not the same (they are – in order: text, decimal number, decimal number, integer). This type of error is called syntax error and the DBMS itself would prevent the query from running at all.

SELECT – Check what was inserted

Now we’ll once more check what is stored in our tables. We’ll use the same two SELECT statements we have used previously:

The result is shown in the picture below. Please notice that after executing queries now we have Results and Messages sections under queries:

We can conclude that both tables in our database contain data and now we’re ready to “play” with something way cooler than this.

INSERT INTO TABLE using Excel

In many cases, you’ll need to run multiple SQL statements based on the dataset provided to you. This stands not only for the INSERT INTO TABLE statement but also for UPDATE and DELETE statements. There is no point in typing these statements manually, but you should rather go with a smarter approach – prepare formulas (or a script) that will automate this part. In such situations, I prefer using Excel and formulas.

Note: Personally, in the context of databases, I find Excel very useful when I need to create multiple statements and when presenting results and/or creating dashboards.

Let’s take a look at these formulas:

The formula used to insert the first country (Germany) is:

The formula used to insert the first city (Berlin) is:

Feel free to use these formulas to automate your tasks. We’ll use a similar approach later when we are running multiple UPDATE and DELETE statements (and even when creating SELECT statements).

Conclusion

In this article, we’ve covered one of the four most important SQL statements –INSERT INTO TABLE statement. We’ve used it to populate tables created in the previous article. This was a prerequisite to move to smarter stuff – like database theory, and more importantly, returning results from our database.

In the upcoming article, we’ll talk about the primary key – what it is and why is it important in the databases.

Table of contents

• Author
• Recent Posts

Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer.

His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...

You can find him on LinkedIn

View all posts by Emil Drkusic

Latest posts by Emil Drkusic (see all)

• Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
• Learn SQL: Dynamic SQL - March 3, 2021
• Learn SQL: SQL Injection - November 2, 2020