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.
|
1 2 3 4 5 6 |
CREATE TABLE department (dno INT PRIMARY KEY, dname VARCHAR(20) NOT NULL, loc VARCHAR(50) NOT NULL ); |
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
|
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO department (dno, dname, loc ) VALUES (10, 'ENGINEERING', 'New York' ); |
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.
|
1 2 |
SELECT * FROM department; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
INSERT INTO department (dno, dname, loc ) VALUES (40, 'Sales', 'NJ' ), (50, 'Marketting', 'MO' ), (60, 'Testing', 'MN' ); |
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.
|
1 2 3 4 |
CREATE TABLE demo (id INT DEFAULT 0, hirdate DATETIME DEFAULT GETDATE() ); |
Now, let us insert default value into the table Demo using a SQL insert into statement
|
1 2 3 4 5 |
INSERT INTO demo DEFAULT VALUES; SELECT * FROM demo; |
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.
|
1 2 3 4 5 |
INSERT INTO demo VALUES(1,'2018-09-28 08:49:00') SELECT * FROM demo; |
Let us consider another example where the table is a combination of both default and non-default columns.
|
1 2 3 4 5 6 7 |
DROP TABLE IF EXISTS Demo; CREATE TABLE demo (id INT PRIMARY KEY IDENTITY(1, 1), Name VARCHAR(20), hirdate DATETIME DEFAULT GETDATE() ); |
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.
|
1 2 3 4 5 |
INSERT INTO demo (name) VALUES ('Prashanth'), ('Brian'), ('Ahmad'); SELECT * FROM demo; |
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
|
1 2 |
INSERT INTO demo(name,hirdate) VALUES('Kiki',DEFAULT), ('Yanna',DEFAULT), ('Maya',DEFAULT); |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SET IDENTITY_INSERT Demo ON; INSERT INTO demo (id, name, hirdate ) VALUES (100, 'Bojan', DEFAULT ), (101, 'Milan', DEFAULT ), (102, 'Esat', DEFAULT ); SET IDENTITY_INSERT Demo OFF; SELECT * FROM demo; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TABLE Demo (FirstName VARCHAR(25), LastName VARCHAR(25), JobTitle VARCHAR(100), Rate DECIMAL(7, 4), GroupName VARCHAR(50) ); INSERT INTO Demo SELECT P.FirstName, P.LastName, EMP.JobTitle, EPH.Rate, Dept.GroupName FROM HumanResources.EmployeePayHistory EPH INNER JOIN HumanResources.Employee EMP ON EMP.BusinessEntityID = EPH.BusinessEntityID INNER JOIN HumanResources.EmployeeDepartmentHistory H ON EMP.BusinessEntityID = H.BusinessEntityID INNER JOIN HumanResources.Department Dept ON H.DepartmentID = Dept.DepartmentID INNER JOIN Person.Person P ON P.BusinessEntityID = EMP.BusinessEntityID WHERE Dept.GroupName = 'Research and Development'; SELECT * FROM Demo; |
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.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE tb_spaceused (database_name NVARCHAR(128), database_size VARCHAR(18), [unallocated space] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18) ); |
The INSERT INTO SQL statement uses the EXECUTE clause to invoke a stored procedure that contains the result set of the SELECT statement.
|
1 2 3 4 5 6 |
INSERT INTO tb_spaceused EXEC sp_msforeachdb @command1 = "use ? exec sp_spaceused @oneresultset = 1"; SELECT * FROM tb_spaceused; |
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).
|
1 2 3 4 |
SELECT * INTO DEMO_Duplicate FROM Demo WHERE 1 <> 2; |
|
1 2 |
SELECT * FROM DEMO_Duplicate |
|
1 |
sp_help 'DEMO_Duplicate' |
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.
|
1 2 3 |
SELECT * INTO DEMO_Duplicate FROM Demo |
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.
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
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021