[fix]-SELECT INTO must be the first query in a statement
SELECT INTO must be the first query in a statement containing a UNION, INTERSECT or EXCEPT operator.
Scenario:
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 as shown below:
SELECT 'MP' AS [StateCode], 'Madhya Pradesh' AS [StateName]
UNION ALL
SELECT 'TN' AS [StateCode], 'Tamil Nadu' AS [StateName]
UNION ALL
SELECT 'AP' AS [StateCode], 'Andhra Pradesh' AS [StateName]
INTO [dbo].[wikitechy_States]
Error:
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.
Fix:
SELECT 'MP' AS [StateCode], 'Madhya Pradesh' AS [StateName]
INTO [dbo].[wikitechy_States]
UNION ALL
SELECT 'TN' AS [StateCode], 'Tamil Nadu' AS [StateName]
UNION ALL
SELECT 'AP' AS [StateCode], 'Andhra Pradesh' AS [StateName]
Now issue a select statement to view the table data as follows:
SELECT * FROM [dbo].[wikitechy_States]
Output:
StateCode StateName
-------------------------------------------
MP Madhya Pradesh
TN Tamil Nadu
AP Andhra Pradesh
For SELECT INTO command with INTERSECT operator, the query is as follows:
SELECT [StudentName] INTO [dbo].[BComAndBSc]
FROM [dbo].[wikitechy_Students] WHERE [Course] = 'BCom'
INTERSECT
SELECT [StudentName] FROM [dbo].[wikitechy_Students]
WHERE [Course] = 'BSc'
Now issue a select query as shown below:
SELECT * FROM [dbo].[BComAndBSc]
Output:
StudentName
---------------------
Ritesh Kumar
For SELECT INTO command with the EXCEPT operator, the query will be like:
SELECT [StudentName] INTO [dbo].[Freshers]
FROM [dbo].[FirstYear]
EXCEPT
SELECT [StudentName] FROM [dbo].[SecondYear]
Now issue a select query as shown below:
SELECT * FROM [dbo].[Freshers]
Output:
StudentName
----------------------
Donald Duck
Pluto
Fixes are applicable to the following versions of SQL Server:
- SQL Server 7.0 OLAP Tools
- SQL Server 2000 64-bit Edition
Related Error Tags:
- How to insert into one table from multiple table while using UNION
- SELECT INTO must be the first query?
- All queries combined using a UNION, INTERSECT or EXCEPT
- Learn to use Union, Intersect, and Except Clauses
- Error – 205 – All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists
