SQL Insert: The Best Way to Populate Database Tables

What Is SQL Insert?

The “INSERT INTO” command is a part of the Data Manipulation Language (DML), a sublanguage of SQL that enables modification and retrieval of information from database objects. This command enables sus to insert rows into tables.

Using this command, you can insert values into all columns or selected columns of a table. This insertion can be executed in an existing table—or a table you create using the “CREATE TABLE” command.

Let’s gain insight into the syntax of the SQL insert command.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Syntax of the SQL Insert INTO Command

There are two syntaxes for the “INSERT INTO” command. The first syntax is as follows:

• The “INSERT INTO” statement lets the database system know that you wish to insert rows into the table that  the table_name parameter specifies 
• Specify the table columns you want to insert values into inside brackets. Use commas to separate the columns
• The “VALUES” statement lets the database system know that the values to be inserted are being specified
• Each column’s respective values are then specified in the same order as the columns inside brackets—using commas to separate these values

If you wish to insert values into all of the table’s columns, it is unnecessary to specify the columns in the command. You can use the following syntax for this purpose:

• Here, it is important to ensure that all specified values are in the correct order, corresponding to their respective columns in the table

The values being inserted should be data types that match what their respective columns during table creation defined. 

Let’s try to populate an entire table using these concepts.

Inserting Values Into All the Columns of a Table

• The first thing we’ll do is create our own table using the “CREATE TABLE” command

We’ll create a table named “Employee” using the following query:

As you can see, “EmployeeID” is the primary key, and “Name” has the NULL CONSTRAINT defined on it, so neither of these attributes can be left blank during insertion. Additionally, “EmployeeID” cannot have the same value for multiple rows.

“Name” and “City” will contain character string data types, so during insertion, the values will be enclosed in single inverted commas. The values won't be accepted otherwise. 

• Let’s insert values into our table using the following query:

If any of the rules are not followed, the system will display an error message explaining what the problem is. For example, if we try to insert another record with the “EmployeeID” column as one, it will result in the following:

• Let’s insert more valid values into our “Employee” table

• To see our table, we’ll use the following query:

This will result in the following:

Sometimes, we don’t have the information about all of the attributes and want to insert values for only a few columns. Let’s see how this can be achieved.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Inserting Values Into Specific Columns

In our “Employee” table, EmployeeID is the primary key, and “Name” has a NULL CONSTRAINT, so we need to enter explicit values for each column’s row. The “City” and “Salary” columns can contain NULL values.

• For example, if if you only have ID and name information for a specific employee, you can use the following query to insert these values:

When we don’t insert an exact value for a NULL CONSTRAINT attribute like “Name,” we see the following error:

As this error message demonstrates, we have to enter explicit values for every record in these columns with constraints.

• Let’s insert additional rows in our table using the following query:

• To see the table, we’ll use the SELECT command.

If you don’t enter an explicit value for any of the records in a column without constraints, they are represented as NULL values, as the default value for any column without constraints is “NULL”.

Sometimes, we require the values of one table to be copied to another table. Let’s see how this can be done.

Learn 15+ In-Demand Tools and Skills!

Automation Testing Masters ProgramExplore Program

Populating a Table Using Another Table

You can populate a table using another table with the “INSERT INTO SELECT” command. The syntax of this command is as follows:

• The destination_table parameter specifies the table we want to insert the values in, and the source_table parameter specifies the table the values are being inserted from
• Here, we need to ensure that we only copy data types from columns that come from the same source and destination tables
• The columns that have a primary key or NOT NULL constraints in the source table also need to be present in the destination table and copied
• This command contains an optional “WHERE” clause 

From the source table, you can specify the condition according to which rows should be selected in the “condition” parameter of this clause. If this is not present in the query, all of the columns’ rows are inserted into the destination table.

For example, if we want to insert values from our “Employee” table “ into a “Salary” table that has only three columns (EmployeeID, Name, and Salary), we’ll use the following query:

To view our “Salary” table, we’ll use the SELECT command as follows:

This shows that all values from the Employee table’s specified columns have been inserted into the Salary table. 

You can also insert records from all columns of a table into another table. The syntax of this command is as follows:

For example, if we duplicate our Employee table and name it “EmployeeCopy,” and this copy includes the same columns from the original, we can use the following query to insert the values of all columns from the original table to the new table:.

To view our new table, we’ll use the SELECT command.

Now that we know the different ways to insert rows into a table, let’s see how to delete these rows.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Deleting Rows From a Table

Sometimes, we need to delete some (or all) of the rows from a table. This can be done with the help of the “DELETE” command, which is a part of the Data Manipulation Language. The syntax of this command is as follows:

This command utilizes the “WHERE” clause. You can specify the condition according to which rows should be deleted in the “condition” parameter of this clause. If this is not present in the query, all the table’s rows will be deleted.

For example, if we want to delete all employee records with salaries less than or equal to 25000 from our “Employee” table, we’ll use the following query:

This will result in the following table:

As this shows, two rows have been deleted.

If we want to delete all rows from the table, we’ll use the following query:

All rows from this table are deleted, but the table can still be used to insert new rows.

Next Steps

In conclusion, it is crucial to insert the right types of values into columns, as this is the data you’re going to be working with. Any mistakes in insertion could potentially create some errors.

Now that you know how to populate tables, the next step in your SQL journey would be to start querying data to retrieve useful information. If you liked this article and want to get certified, check out Simplilearn’s Business Analyst Master’s Program, which covers SQL in great depth.

Do you have any questions for us? Leave a comment, and we’ll have our experts in the field answer them for you.