Working with the INSERT statement in SQL Server

The INSERT statement lets you add one or more rows to a table or view in a SQL Server database. The statement is one of the primary data modification language (DML) statements available in Transact-SQL, along with UPDATE, MERGE, and DELETE. You can use the INSERT statement to add data that you specifically define, or you can add data that you retrieve from other tables or views. You can also include an OUTPUT clause in your INSERT statement to capture the statement’s results for auditing or verification purposes.
To demonstrate how to create a basic INSERT statement, I first used the following code to create the abc table
IF OBJECT_ID(‘abc’, ‘U’) IS NOT NULL

DROP TABLE abc;
CREATE TABLE abc
(
StaffID INT NOT NULL PRIMARY KEY,
FirstName NVARCHAR(30) NOT NULL,
LastName NVARCHAR(30) NOT NULL
);

INSERT INTO abc VALUES (1, ‘Stephen’, ‘Jiang’);

you might want to add multiple rows to a table in a single statement. Prior to SQL Server 2008,

INSERT abc VALUES
(2, ‘Michael’, ‘Blythe’),
(3, ‘Linda’, ‘Mitchell’),
(4, ‘Jillian’, ‘Carson’),
(5, ‘Garrett’, ‘Vargas’);

IF OBJECT_ID (‘abcd’, ‘U’) IS NOT NULL
DROP TABLE abcd;
CREATE TABLE abcd
(
StaffID INT NOT NULL IDENTITY PRIMARY KEY,
StaffGUID UNIQUEIDENTIFIER NULL,
FirstName NVARCHAR(30) NOT NULL,
LastName NVARCHAR(30) NOT NULL,
FullName AS (FirstName + ‘ ‘ + LastName),
ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()
);
StaffID is an IDENTITY column, FullName is a calculated column, and ModifiedDate has been defined with a default value (retrieved through the GETDATE function). Now let’s look at an INSERT statement that provides values for the other three columns—StaffGUID, FirstName, LastName

INSERT INTO abcd
(StaffGUID, FirstName, LastName)
VALUES (NEWID(), ‘Stephen’, ‘Jiang’);
SELECT StaffID, StaffGUID, FullName, ModifiedDate
FROM abcd;

IF OBJECT_ID (‘xyz’, ‘U’) IS NOT NULL
DROP TABLE xyz;
CREATE TABLE xyz
(
StaffID INT NOT NULL PRIMARY KEY,
FullName NVARCHAR(60) NOT NULL,
ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()
);

INSERT xyz
(StaffID, FullName)
SELECT TOP 5
staffID,
FirstName + ‘ ‘ + LastName AS FullName
FROM abc
ORDER BY staffID;
SELECT * FROM xyz;

A SELECT statement that’s used within an INSERT statement can reference a common table expression (CTE) as well as a table or view, as long as that CTE precedes the INSERT statement (as opposed to being part of the SELECT statement itself). For instance, in the following example, I first define a CTE and then define the INSERT statement, which pulls data from the CTE through a SELECT statement:
Insertion With CTE
WITH cteabc (StaffID, FullName)
AS
(
SELECT staffID,
FirstName + ‘ ‘ + LastName AS FullName
FROM abc
WHERE staffID BETWEEN 1 AND 3
)
INSERT xyz
(StaffID, FullName)
SELECT StaffID, FullName
FROM cteabc;
SELECT * FROM SalesStaff3
WHERE StaffID BETWEEN 279 AND 283;
————————————–
Instead of using a SELECT statement to retrieve data, you can call a stored procedure in your INSERT statement. For instance, the following stored procedure—spSalesStaff—retrieves data from the vSalesPerson view:
IF OBJECT_ID(‘spabc’, ‘P’) IS NOT NULL
DROP PROCEDURE spabc;
GO
CREATE PROCEDURE spabc
AS
SELECT staffID,
FirstName + ‘ ‘ + LastName AS FullName
FROM abc
WHERE staffID BETWEEN 1 AND 3;
I can then call this stored procedure from within an INSERT statement, as shown in the following example:
INSERT xyz (StaffID, FullName)
EXEC spabc;
SELECT * FROM xyz
WHERE StaffID BETWEEN 1 AND 3;
Starting with SQL Server 2005, DML statements supported the OUTPUT clause, which lets you output the modified table to another table or variable. In the case of the INSERT statement, you can use the OUTPUT clause to track the data that is being inserted into your table. For example, the following INSERT statement uses the OUTPUT clause to output the inserted values to a table variable:

DECLARE @InsertOutput TABLE
(
StaffID INT,
FullName VARCHAR(60)
);
INSERT INTO xyz
(StaffID, FullName)
OUTPUT INSERTED.StaffID, INSERTED.FullName
INTO @InsertOutput

SELECT
staffID,
FirstName + ‘ ‘ + LastName AS FullName
FROM abcd
WHERE staffID> 0;
SELECT * FROM xyz
WHERE StaffID >0;
SELECT * FROM @InsertOutput;

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s