Insert in SQL Server



INSERT in SQL Server is used to add additional row(s) to a table. Now, there are different ways to use INSERT to add rows. Let’s first show a full example demonstrating a couple of these methods and then break this example down and discuss the individual pieces.

Example

/* Create the tables */
CREATE TABLE CustomerTest
(
	Id INT IDENTITY(1, 1),
	Prefix VARCHAR(50) NULL,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,	
	CONSTRAINT PK_Customer PRIMARY KEY (Id)
);

/* VALUES INSERT with 1 row */
INSERT INTO CustomerTest(Prefix, FirstName, LastName)
VALUES ('Mr', 'John', 'Smith');

/* VALUES INSERT with two rows */
INSERT INTO CustomerTest(Prefix, FirstName, LastName)
VALUES 
	('Ms', 'Susan', 'Jones'),
	('Mr', 'Kyle', 'Smith');

/* VALUES INSERT without specifying column names */
INSERT INTO CustomerTest 
VALUES ('Ms', 'Hailey', 'Adams');

/* SELECT INSERT. The information is being pulled from a table variable.
* Notice that we're not specifying a Prefix here. 
* The Prefix will show up as NULL for these rows. */
DECLARE @CustomersToInsert TABLE
(
	Id INT,
	FirstName VARCHAR(100),
	LastName VARCHAR(100)	
);

INSERT INTO @CustomersToInsert(FirstName, LastName)
VALUES
	('Jacob', 'Henry'),
	('Sally', 'Wells');
	
INSERT INTO CustomerTest(FirstName, LastName)
SELECT 
	 FirstName,
	 LastName
FROM 
	@CustomersToInsert;

/* Results */
SELECT * FROM CustomerTest;

/* Clean up the test table */ 
DROP TABLE CustomerTest;

Output

Id Prefix FirstName LastName
1 Mr John Smith
2 Ms Susan Jones
3 Mr Kyle Smith
4 Ms Hailey Adams
5 NULL Jacob Henry
6 NULL Sally Wells

Example Details

Create the tables

We’re first creating a new “CustomerTest” table that can be used to track customer information (just the customers’ name pieces in this case). All columns in this table must be given a value in the INSERT, which is enforced via the NOT NULL and PRIMARY KEY constraints. The exception is “Prefix”, which allows a NULL value.

And if you’re not familiar with IDENTITY, then that’s essentially just a way to provide an auto-incrementing Id so that you don’t have to manually specify it for each new row that we’re adding.

VALUES INSERT

VALUES allows us to specify a row or rows of data to add to a table. VALUES is being used in three ways in the above example:

  1. Insert with 1 row. The table and column names for each of the columns we want to provide a value for are specified on the first line. Note that the order of the columns can be specified however you want but it’s usually best practice to specify them in the same order as they appear in the corresponding table. Also, the Id column is not specified since we’re using IDENTITY for it.
  2. Insert with 2 rows. This is the same as the prior example except there are two rows of data being added. Of course, this isn't limited to two and you can use the same pattern to add as many additional rows of data as needed.
  3. Insert without any columns being specified. You don’t necessarily need to specify the columns in the INSERT. If not provided, then it’s essentially as if you specified all the columns, except IDENTITY, in the table in order. My personal opinion is that column names should always be provided as it makes the INSERT easier to read and decipher.

SELECT INSERT

In this portion of the example, we’re first defining a table variable (@CustomersToInsert) and adding some rows to it using VALUES. This will be used as the data source for the SELECT INSERT.

Next, we’re adding all the values from @CustomersToInsert to our CustomerTest table via a SELECT. Note that we’re not specifying the “Prefix” column here as this column is optional.

Why use SELECT over VALUES? SELECT provides a quick way to grab data from a query and add it to a table. This is especially useful when you’re working with large sets of data. For example, if you wanted to add 100 rows worth of data from a query to another table, a SELECT can be used to do this rather than manually adding 100 rows with VALUES.

On the other hand, if you just need to add a few rows of data to a table, then using VALUES might be easier. It just depends on the context.

Results and Cleanup

There isn’t too much to mention here. We’re just grabbing all the values in the CustomerTest table first via a SELECT. After that the CustomerTest table is being removed.

Additional Resources

If you’re looking for more details around some of the options for an insert in SQL Server, then the following link from Microsoft is a good resource: Microsoft - INSERT (Transact-SQL)