Prashanth Jayaram

Overview of the SQL Insert statement

October 16, 2018 by

This article on the SQL Insert statement, is part of a series on string manipulation functions, operators and techniques. The previous articles are focused on SQL query techniques, all centered around the task of data preparation and data transformation.

So far we’ve been focused on select statement to read information out of a table. But that begs the question; how did the data get there in the first place? In this article, we’ll focus on the DML statement, the SQL insert statement. If we want to create a data, we’re going to use the SQL keyword, “Insert”.

The general format is the INSERT INTO SQL statement followed by a table name, then the list of columns, and then the values that you want to use the SQL insert statement to add data into those columns. Inserting is usually a straightforward task. It begins with the simple statement of inserting a single row. Many times, however, it is more efficient to use a set-based approach to create new rows. In the latter part of the article, let’s discuss various techniques for inserting many rows at a time.

Pre-requisite

The assumption is that you’ve the following the permission to perform the insert operation on a table

  • Insert operation is default to the members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner.
  • Insert with the OPENROWSET BULK option requires a user to be a member of the sysadmin fixed server role or of the bulkadmin fixed server role.
  • Download AdventureWorks2014 here

Rules:

  • Typically we don’t always provide data for every single column. In some cases, the columns can be left blank and in some other provide their own default values. 
  • You also have situations where some columns are automatically generating keys. In such cases, you certainly don’t want to try and insert your own values in those situations.
  • The columns and values must match order, data type and number
  • If the column is of strings or date time or characters, they need to be enclosed in the in the single quotes. If they’re numeric, you don’t need the quotes. 
  • If you do not list your target columns in the insert statement then you must insert values into all of the columns in the table, also, be sure to maintain the order of the values

How to perform a simple Insert

Let’s start inserting the data into this simple department table. First, use the name of the table and then inside parenthesis, the name of the columns and then type in the values. So, name the columns that we are going to type in the values.

The following SQL Insert into statement inserts a row into the department. The columns dno, dname, and loc are listed and values for those columns are supplied. The order is also maintained in the same way as the columns in the table

How to perform a simple Insert using SSMS

Inserting data into a table can be accomplished either using SQL Server Management Studio (SSMS), a GUI, or through Data Manipulation Language in the SQL Editor. Using GUI in SSMS is a quick and easy way to enter records directly to the table.

Let’s go ahead and browse department table and right-click and go to edit top 200 rows.

This will bring up an editor window where we can interact directly with the data.  To type in the new values, come down to the bottom and start typing the values.

It is very useful in some case to familiarize yourself with what data that you’re about to enter into the table.


How to use an Insert into statement to add multiple rows of data

In the following SQL insert into statement, three rows got inserted into the department. The values for all columns are supplied and are listed in the same order as the columns in the table. Also, multiple values are listed and separated by comma delimiter.

How to use an Insert into statement to add data with default values

Let us create a simple table for the demonstration. A table is created with integer column defined with default value 0 and another DateTime column defined with the default date timestamp value.

Now, let us insert default value into the table Demo using a SQL insert into statement


Note: If all the columns of the table defined with default values then specify the DEFAULT VALUES clause to create a new row with all default values

Next, override the default values of the table with a SQL Insert into statement.


Let us consider another example where the table is a combination of both default and non-default columns.

In order to insert default values to the columns, you just need exclude the default columns from the insert list with a SQL insert into statement.


The following example you can see that the keyword DEFAULT is used to feed a value to the table in the values clause with a SQL Insert into statement


How to use an Insert to add data to an identity column table

The following example shows how to insert data to an identity column. In the sample, we are overriding the default behavior (IDENTITY property of the column) of the INSERT with the SET IDENTITY_INSERT statement and insert an explicit value into the identity column. In this case, three rows are inserted with the values 100, 101 and 102


How to use a SQL insert statement to add data from another dataset

In this section, we’ll see how to capture the results of a query (simple select or multi table complex select) into another table.

The following example shows how to insert data from one table into another table by using INSERT…SELECT or INSERT…EXECUTE or SELECT * INTO . Each is based on a multi-table SELECT statement that includes an expression and a literal value in the column list.

INSERT…SELECT statement

The first SQL INSERT statement uses an INSERT…SELECT statement to derive the output from the multiple source tables such as Employee, EmployeePayHistory, Department, and Person of the AdventureWorks2014 database and insert the result set into the demo table.

You can see that schema and definition is already built for the INSERT INTO SQL statement.


INSERT…EXECUTE statement

The second INSERT… EXECUTE statement, the stored procedure is executed and that contains the SELECT statement. The following example, the tb_spaceused table is created.

The INSERT INTO SQL statement uses the EXECUTE clause to invoke a stored procedure that contains the result set of the SELECT statement.


SELECT * INTO statement

The third, in this case, you want to create a new table having the same set of columns as an existing table or simple select or complex select statement.

Copy schema only

For example, you may want to create just the structure of the demo table and call it demo_ duplicate and you don’t want to the copy the rows. In this case, use FALSE condition in the WHERE clause (1 <>2 or 1=0).



Note: In this case, the demo table is already created in the first method. I’m using the same table for this demonstration.

Copy schema and data

The following example copies both schema and data to the target table.


Summary

Thus far, we discussed standards, rules, guidelines for the SQL Insert statement. You could insert any values if it’s coupled with select statement and matches with the target schema. Thanks for reading this article and if you have any questions, feel free to ask in the comments below.


Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
T-SQL

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

234 Views