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?

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.
zimmer9Asked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
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
 
ShazbotOKCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>SELECT ProdID = IDENTITY(INT, 1,1),
Exactly what are you trying to pull off with the above code snippet?
0
 
zimmer9Author Commented:
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
 
ShazbotOKCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.