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
903 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 69

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now