[fix]-SELECT INTO must be the first query in a statement

Wikitechy | 2277 Views | sql server | 09 Jun 2016

 

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 1.1 (16 bit)
  • SQL Server 4.2A (16 bit)
  • SQL Server 4.2B (16 bit)
  • SQL Server 4.21a
  • SQL Server 6.0
  • SQL Server 6.5
  • SQL Server 7.0
  • SQL Server 7.0 OLAP Tools
  • SQL Server 2000
  • SQL Server 2000 64-bit Edition
  • SQL Server 2005
  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

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



Workshop

Bug Bounty
IOT Hackathon
Webinar

Join our Community

Advertise