|
|
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.
|