|
|
Error Message
Server: Msg 194, Level 15, State 1, Line 1
A SELECT INTO statement cannot contain a SELECT
statement that assigns values to a variable.
Causes
As the message suggests, the SELECT part of a SELECT INTO statement contains both the columns to be inserted into another table as well as the assigning of values to variables. To illustrate, here’s a script that will generate this error message:
|
DECLARE @RowsInserted INT
DECLARE @DateInserted DATETIME
SELECT *, @RowsInserted = @@ROWCOUNT, @DateInserted = GETDATE()
INTO [dbo].[CustomersNY]
FROM [dbo].[Customers]
WHERE [State] = 'NY'
Msg 194, Level 15, State 1, Line 4
A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable.
This script tries to create another table containing customers from New York using the SELECT INTO statement. In the same SELECT statement, it also tries to assign the number of rows inserted into the new table as well as the date and time the records were inserted into the table.
Solution / Work Around:
To avoid this error, the SELECT statement that assigns a value to a variable needs to be done as a separate step from the SELECT INTO statement. Given the sample SELECT INTO statement earlier, here’s how the script will look like which avoids this error message:
DECLARE @RowsInserted INT
DECLARE @DateInserted DATETIME
SELECT *
INTO [dbo].[CustomersNY]
FROM [dbo].[Customers]
WHERE [State] = 'NY'
SELECT @RowsInserted = @@ROWCOUNT, @DateInserted = GETDATE()
The @@ROWCOUNT function returns the number of rows affected by the last statement. In this particular case, it contains the number of rows inserted into the newly created table. The saving of the value of @@ROWCOUNT needs to be done immediately after the SELECT INTO statement; otherwise, it will contain a different value. To illustrate, let’s assume that the date and time when the SELECT INTO statement was executed was saved first followed by the saving of the number of rows inserted into the new table:
DECLARE @RowsInserted INT
DECLARE @DateInserted DATETIME
SELECT *
INTO [dbo].[CustomersNY]
FROM [dbo].[Customers]
WHERE [State] = 'NY'
SET @DateInserted = GETDATE()
SET @RowsInserted = @@ROWCOUNT
SELECT @RowsInserted AS [RowsInserted]
In this particular script, the @@ROWCOUNT function will return a value of 1 instead of the number of rows inserted into the new table because the last statement executed just before saving its value is SET @DateInserted = GETDATE(). Statements that make a simple assignment always set the @@ROWCOUNT value to 1.
|