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
940 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 66

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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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