Solved

How to avoid the error "The IDENTITY function can only be used when the SELECT statement has an INTO clause" in a SQL Server 2005 statment?

Posted on 2013-11-08
5
921 Views
Last Modified: 2013-11-08
Do you know how the following query could be rewritten to avoid the following error in SQL Server 2005 when INSERTING records into a table?
--------------------------------------------------------------------------------------


If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblRemedInternal' AND TYPE = 'U')
DELETE FROM dbo.tblRemedInternal
INSERT INTO dbo.tblRemedInternal
SELECT ProdID = IDENTITY(INT, 1,1), Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number],
...

FROM dbo.tblCustomersNew AS C
LEFT JOIN dbo.tblStatesAll AS S ON S.StateFS = C.ResStateCode                                
INNER JOIN dbo.tblProductsNew AS P ON C.CustomerNumber = P.CustomerNumber  AND C.OfficeNumber = P.OfficeNumber
WHERE (S.FallCycle= 0)

UNION ALL
INSERT INTO dbo.tblRemedInternal
SELECT ProdID = IDENTITY(INT, 1,1), Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number],
...

FROM tblCustomers AS C
LEFT JOIN tblStatesAll AS S ON S.StateFS = C.ResStateCode                                
INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber
WHERE S.StateFS IS NULL
---------------------------------------------------------------------------------------------

Msg 177, Level 15, State 1, Procedure procSpringInternalAll, Line 0
The IDENTITY function can only be used when the SELECT statement has an INTO clause.
Msg 177, Level 15, State 1, Procedure procSpringInternalAll, Line 0
The IDENTITY function can only be used when the SELECT statement has an INTO clause.
0
Comment
Question by:zimmer9
5 Comments
 
LVL 12

Expert Comment

by:ShazbotOK
ID: 39633556
Here is an example for an INTO clause:

USE AdventureWorks2012;
GO
SELECT c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, a.City,
    sp.Name AS [State/Province], a.PostalCode
INTO dbo.EmployeeAddresses
FROM Person.Person AS c
    JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = c.BusinessEntityID
    JOIN Person.BusinessEntityAddress AS bea
    ON e.BusinessEntityID = bea.BusinessEntityID
    JOIN Person.Address AS a
    ON bea.AddressID = a.AddressID
    JOIN Person.StateProvince as sp
    ON sp.StateProvinceID = a.StateProvinceID;
GO
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39633577
>SELECT ProdID = IDENTITY(INT, 1,1),
Exactly what are you trying to pull off with the above code snippet?
0
 

Author Comment

by:zimmer9
ID: 39633597
I am using Access 2003 and Excel 2003 so I would use the following code to put 60,000
records into each worksheet of a workbook because of the 65,535 record limitation:

Private Sub ExportToExcels(filename As String)
Dim str_sql As String
Dim cn As ADODB.Connection
Dim xl As Excel.Application
Dim xlWB As Excel.Workbook
Dim sht As Excel.Worksheet, rng As Excel.Range
Dim db As DAO.Database, rs As ADODB.Recordset
Dim recordtotal As Long
Dim SheetNum As Long
Dim dest As Range
Dim Counter As Long
Dim Source As Workbook
Dim col As Long
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set db = CurrentDb

recordtotal = DCount("ProdID", "tblRemedInternal")
Set xl = CreateObject("Excel.Application")

Set xlWB = xl.Workbooks.Add ' Add a new workbook
xlWB.SaveAs filename ' Save the new workbook as "filename"
Set xlWB = xl.Workbooks.Open(filename)  '<--Your Excel File Here


xl.Visible = True
SheetNum = 1
Do While recordtotal > 0
    rs.Open "Select top 60000 * from tblRemedInternal", cn, 2, 2
    If rs.EOF Then Exit Sub
    rs.MoveFirst
        Set sht = Nothing
        On Error Resume Next
        Set sht = xlWB.Worksheets("Sheet" & SheetNum)
        On Error GoTo 0
               
        If sht Is Nothing Then
            With xlWB
               .Worksheets.Add After:=.Worksheets(.Worksheets.count)
               Set sht = .Worksheets(.Worksheets.count)
               sht.Name = "Sheet" & SheetNum
            End With
        End If              
             
        For col = 0 To rs.Fields.count - 1
            sht.Cells(1, col + 1).Value = rs.Fields(col).Name
        Next
        sht.Range("A2").CopyFromRecordset rs
        SheetNum = SheetNum + 1
        str_sql = "delete from tblRemedInternal where ProdID in(Select top 60000 ProdID from tblRemedInternal)"
        DoCmd.RunSQL (str_sql)
    rs.Close
    recordtotal = DCount("ProdID", "tblRemedInternal")
Loop

xlWB.Close (True)
xl.Quit
Set xl = Nothing

End Sub
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 39633629
a few things:

this doesn't make sens (the INSERT statement shouldn't appear there):
UNION ALL
INSERT INTO dbo.tblRemedInternal
SELECT ProdID = IDENTITY(INT...

You normally have to do a;

insert into ... (fields name)
select * from (
select * from t1 ...
union all
select * from t2 ...)
as A

In your "fields name", specify the fields but do not put the Identity field.
0
 
LVL 12

Expert Comment

by:ShazbotOK
ID: 39633740
INSERT INTO is the problem if your wanting to force an identity insert.  You have to setup the query with SELECT  IDENTITY(INT, 1,1), (blah) INTO dbo.tblRemedInternal
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Count with a subquery showing details 10 43
sql server insert 12 30
Access check if a table is open 4 41
2 IIF's in Access query 25 23
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question