Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
Medium Priority
?
981 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 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

564 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