SQL Server Error Messages - Msg 196

Error Message

Msg 196, Level 15, State 1, Line 1
SELECT INTO must be the first query in a statement containing a UNION, INTERSECT or EXCEPT operator.

Causes

This error happens when creating a new table using the SELECT INTO command and the table being created comes from different sources and is put together using the UNION, UNION ALL, INTERSECT or EXCEPT operators.

The UNION or UNION ALL operator combines the results of two or more queries into a single result set that includes all rows that belong to all queries in the union. The INTERSECT operator returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand. The EXCEPT operator returns any distinct values from the left query that are not also found on the right query.

To illustrate on how the error is encountered, the following SELECT INTO statement together with the UNION ALL operator will generate the error:

SELECT 'FL' AS [StateCode], 'Florida' AS [StateName]
UNION ALL
SELECT 'CA' AS [StateCode], 'California' AS [StateName]
UNION ALL
SELECT 'NY' AS [StateCode], 'New York' AS [StateName]
INTO [dbo].[USStates]

Msg 196, Level 15, State 1, Line 1
SELECT INTO must be the first query in a statement containing a UNION, INTERSECT or EXCEPT operator.

Here’s an example of how the error can be encountered using the SELECT INTO statement together with the INTERSECT operator. The following script tries to create a table of students taking up both Biology and Chemistry classes:

CREATE TABLE [dbo].[StudentClass] (
    [StudentName]           VARCHAR(100),
    [ClassName]             VARCHAR(50)
)

INSERT INTO [dbo].[StudentClass] ( [StudentName], [ClassName] )
VALUES ( 'Mickey Mouse', 'Biology' )

INSERT INTO [dbo].[StudentClass] ( [StudentName], [ClassName] )
VALUES ( 'Mickey Mouse', 'Chemistry' )

INSERT INTO [dbo].[StudentClass] ( [StudentName], [ClassName] )
VALUES ( 'Donald Duck', 'Biology' )

INSERT INTO [dbo].[StudentClass] ( [StudentName], [ClassName] )
VALUES ( 'Donald Duck', 'Anatomy' )

SELECT [StudentName] 
FROM [dbo].[StudentClass]
WHERE [ClassName] = 'Biology'
INTERSECT
SELECT [StudentName]
INTO [dbo].[BiologyAndChemistryStudents]
FROM [dbo].[StudentClass]
WHERE [ClassName] = 'Chemistry'

Msg 196, Level 15, State 1, Line 1
SELECT INTO must be the first query in a statement containing a UNION, INTERSECT or EXCEPT operator.

And lastly, here’s an example on how the error can be encountered using the SELECT INTO statement together with the EXCEPT operator. The following script tries to create table containing all new students this year.

CREATE TABLE [dbo].[StudentsThisYear] (
    [StudentName]        VARCHAR(100)
)

CREATE TABLE [dbo].[StudentsLastYear] (
    [StudentName]        VARCHAR(100)
)

INSERT INTO [dbo].[StudentsThisYear] ( [StudentName] )
VALUES ( 'Mickey Mouse')

INSERT INTO [dbo].[StudentsThisYear] ( [StudentName] )
VALUES ( 'Donald Duck')

INSERT INTO [dbo].[StudentsThisYear] ( [StudentName] )
VALUES ( 'Pluto')

INSERT INTO [dbo].[StudentsLastYear] ( [StudentName] )
VALUES ( 'Mickey Mouse')

SELECT [StudentName]
FROM [dbo].[StudentsThisYear]
EXCEPT
SELECT [StudentName]
INTO [dbo].[NewStudents]
FROM [dbo].[StudentsLastYear] 

Msg 196, Level 15, State 1, Line 21
SELECT INTO must be the first query in a statement containing a UNION, INTERSECT or EXCEPT operator.

Solution / Workaround

As the message suggests, to avoid this error when using the SELECT INTO command together with the UNION, UNION ALL, INTERSECT or EXCEPT operators, the INTO clause must be after the first query in the statements.

Using the example above, the correct way to use the SELECT INTO with the UNION ALL operator is as follows:

SELECT 'FL' AS [StateCode], 'Florida' AS [StateName]
INTO [dbo].[USStates]
UNION ALL
SELECT 'CA' AS [StateCode], 'California' AS [StateName]
UNION ALL
SELECT 'NY' AS [StateCode], 'New York' AS [StateName]

SELECT * FROM [dbo].[USStates]

StateCode   StateName
----------- -----------
FL          Florida
CA          California
NY          New York

In the case of the SELECT INTO command with the INTERSECT operator, the corrected syntax is as follows:

SELECT [StudentName] 
INTO [dbo].[BiologyAndChemistry]
FROM [dbo].[StudentClass]
WHERE [ClassName] = 'Biology'
INTERSECT
SELECT [StudentName]
FROM [dbo].[StudentClass]
WHERE [ClassName] = 'Chemistry'

SELECT * FROM [dbo].[BiologyAndChemistry]

StudentName
-------------
Mickey Mouse

Lastly, in the case of the SELECT INTO command with the EXCEPT operator, the corrected syntax is as follows:

SELECT [StudentName]
INTO [dbo].[NewStudents]
FROM [dbo].[StudentsThisYear]
EXCEPT
SELECT [StudentName]
FROM [dbo].[StudentsLastYear] 

SELECT * FROM [dbo].[NewStudents]

StudentName
--------------
Donald Duck
Pluto