Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Example Uses of the LEFT String Function
Error Messages
Home > SQL Server Error Messages > Msg 199 - An INSERT statement cannot contain a SELECT statement that assigns values to a variable.
SQL Server Error Messages - Msg 199 - An INSERT statement cannot contain a SELECT statement that assigns values to a variable.

Error Message

Server: Msg 199, Level 15, State 1, Line 1
An INSERT statement cannot contain a SELECT statement 
that assigns values to a variable.

Causes

As the message suggests, this error occurs when inserting new records in an existing table using the INSERT INTO ... SELECT statement but the SELECT statement assigns the values being inserted to local variables. To better illustrate, here’s a script that will show how the error message can be generated:

CREATE TABLE [dbo].[Customers] (
    [CustomerID]      INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [CustomerName]    VARCHAR(100),
    [Address]         VARCHAR(100),
    [City]            VARCHAR(50),
    [State]           CHAR(2),
    [ZIPCode]         VARCHAR(5)
)
GO

CREATE TABLE [dbo].[CustomersNY] (
    [CustomerID]      INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [CustomerName]    VARCHAR(100),
    [Address]         VARCHAR(100),
    [City]            VARCHAR(50),
    [State]           CHAR(2),
    [ZIPCode]         VARCHAR(5)
)
GO

DECLARE @CustomerName    VARCHAR(100)
DECLARE @Address         VARCHAR(100)
DECLARE @City            VARCHAR(50)
DECLARE @StateCode       CHAR(2)
DECLARE @ZIPCode         VARCHAR(5)

INSERT INTO [dbo].[CustomersNY] ( [CustomerName], [Address], [City], [State], [ZIPCode] )
SELECT TOP 1 @CustomerName = [CustomerName], @Address = [Address], @City = [City],
             @StateCode = [State], @ZIPCode = [ZIPCode]
FROM [dbo].[Customers]
WHERE [State] = 'NY'

Msg 199, Level 15, State 1, Line 0
An INSERT statement cannot contain a SELECT statement that assigns values to a variable.

As can be seen from the INSERT INTO … SELECT statement, each column in the SELECT statement is first assigned to a corresponding local variable before being inserted into a table and doing so generates this error message.

Solution / Work Around:

To avoid from encountering this error message, the assigning of the values into the local variables has to be separated from the insertion of the same values into a table. The following script separates the two processes to accomplish the intended task:

DECLARE @CustomerName    VARCHAR(100)
DECLARE @Address         VARCHAR(100)
DECLARE @City            VARCHAR(50)
DECLARE @StateCode       CHAR(2)
DECLARE @ZIPCode         VARCHAR(5)

SELECT TOP 1 @CustomerName = [CustomerName], @Address = [Address], @City = [City],
             @StateCode = [State], @ZIPCode = [ZIPCode]
FROM [dbo].[Customers]
WHERE [State] = 'NY'

INSERT INTO [dbo].[CustomersNY] ( [CustomerName], [Address], [City], [State], [ZIPCode] )
VALUES ( @CustomerName, @Address, @City, @State, @ZIPCode )
GO

No shortcut can be used to perform this task and combining these two steps into one as can be seen from the following script, which generates a different error message:

DECLARE @CustomerName    VARCHAR(100)
DECLARE @Address         VARCHAR(100)
DECLARE @City            VARCHAR(50)
DECLARE @StateCode       CHAR(2)
DECLARE @ZIPCode         VARCHAR(5)

INSERT INTO [dbo].[CustomersNY] ( [CustomerName], [Address], [City], [State], [ZIPCode] )
SELECT TOP 1 [CustomerName], [Address], [City], [State], [ZIPCode],
       @CustomerName = [CustomerName], @Address = [Address], @City = [City],
       @StateCode = [State], @ZIPCode = [ZIPCode]
FROM [dbo].[Customers]
WHERE [State] = 'NY'

Msg 141, Level 15, State 1, Line 0
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval 
operations.
Related Articles :