Sunday, January 18, 2015

An INSERT statement cannot contain a SELECT statement that assigns values to a variable

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

This is the Error message which we generally encounter while writing the insert statement integrated with select statement.

For example check below code..

INSERT INTO [dbo].[Employee] ( [EmployeeName], [Address], [City], [State], [ZIPCode] )
SELECT TOP 1 @EmployeeName = [EmployeeName], @Address = [Address], @City = [City],
             @StateCode = [State], @ZIPCode = [ZIPCode]
FROM [dbo].[Employee]
WHERE [State] = 'HR'

In this same time you are assigning while inserting and it will throw Error.

Reason : So  now you were wondering the why this is happening. SQL Server generates a single query execution plan for a query batch. so if you notice above query you will be able to identify that variable is assigning value which are not available i.e. its for executing this there should be two plan one before assign the value other after assigning value but in this syntax its not happening thats why its throwing error.

Solution: do both operation in different query. First assign then insert.

No comments:

Post a Comment