INSERT INTO with SELECT DISTINCT causes a zero value to be inserted into an IDENTITY column

INSERT INTO with SELECT DISTINCT causes a zero value to be inserted into an IDENTITY column

Post by Jim Coughli » Tue, 20 May 1997 04:00:00



Following is an SQL Server 6.5 service pack release 2 script.
The bug is as follows.

THE SET IDENTITY_INSERT WorkCenterPerformance is OFF.

The INSERT INTO with SELECT DISTINCT causes a zero value to be inserted
into
table WorkCenterPerformance.WorkCenterPerformanceID column no matter how
many rows
currently exist in table WorkCenterPerformance.

My understanding is that the value that should be inserted into the
identity column is 1
greater than the last value inserted.

This bug is re-creatable and I can make it fail every time.

This bug was discovered while executing a stored procedure that was invoked
from a trigger.

If you would like further info, e-mail me.

Sincerely, Jim
****************************************************************************
***********

create table WorkCenterPerformance
(
    WorkCenterPerformaceID        int                   identity,
    WorkCenterID                  smallint              null    ,
    WorkOrderExplodedLinesID      int                   null    ,
    PrintItemSpecID               int                   null    ,
    WorkOrderReleaseNo            T_WorkOrderReleaseNo  null    
        default 0
        constraint CKC_WORKORDERRELEASEN_WORKCENT check (WorkOrderReleaseNo

Quote:>= 0),

    Priority                      tinyint               null    
        constraint CKC_PRIORITY_WORKCENT check (Priority >= 0),
    SupervisorStopWorkCode        tinyint               null    ,
    TotalQuantityCompleted        T_WholeQuantity       not null
        default 0
        constraint CKC_TOTALQUANTITYCOMP_WORKCENT check
(TotalQuantityCompleted >= 0),
    CurrentQuantityToMake         T_WholeQuantity       not null
        default 0
        constraint CKC_CURRENTQUANTITYTO_WORKCENT check
(CurrentQuantityToMake >= 0),
    StateOfWorkCenter             tinyint               not null
        default 0
        constraint CKC_STATEOFWORKCENTER_WORKCENT check (StateOfWorkCenter
in (0,1,2,3,4)),
    StateOfWork                   tinyint               not null
        default 0
        constraint CKC_STATEOFWORK_WORKCENT check (StateOfWork in
(0,1,2,3)),
    WorkOrderStatusID             tinyint               null    
        default 2,
    MostRecentStartTime           datetime              null    ,
    MostRecentStopTime            datetime              null    ,
    MostRecentHaltTime            datetime              null    ,
    ConcurrencyCount              timestamp             not null,
    LastModifiedTime              T_LastModifiedTime    not null,
    LastModifiedBy                T_LastModifiedBy      not null,
    constraint PK_WORKCENTERPERFORMANCE primary key
(WorkCenterPerformaceID)
)

/*
 THIS STATEMENT CAUSES the value of zero to be inserted into column
WorkCenterPerformanceID.
 I believe that this is a Microsoft BUG!!!
*/
INSERT INTO WorkCenterPerformance
 ( WorkCenterID
  ,WorkOrderExplodedLinesID
  ,PrintItemSpecID
  ,CurrentQuantityToMake
  ,WorkOrderStatusID
 )
 SELECT DISTINCT        
   300
  ,SB.WorkOrderExplodedLinesID
  ,NULL
  ,SB.OverQuantity                  /* Quantity to make */
  ,2                              /* RELEASED (status) */
 FROM                   #S2_Books                       SB
   INNER JOIN           BookSpec                        BS      ON SB.PartID                    = BS.BookSpecPartID
   LEFT OUTER JOIN      BookComponentSpec               BCS     ON BS.BookSpecPartID            =
BCS.BookSpecPartID
   LEFT OUTER JOIN      BookSpecOperations      BSO     ON BS.BookSpecPartID            =
BSO.BookSpecPartID
   LEFT OUTER JOIN      SpecOperWorkCenter      SOW     ON BSO.SpecOperWorkCenterID     =
SOW.SpecOperWorkCenterID
 WHERE
      BS.BindingTypeID IS NOT NULL
  OR  BCS.PrintBlockTypeID IN(1,2,5)  /* Book Cover(Front or One Piece)
,Book Text ,Book Cover (Rear) */
  OR  SOW.WorkCenterID = 300          /*  300 = BINDERY */

 
 
 

INSERT INTO with SELECT DISTINCT causes a zero value to be inserted into an IDENTITY column

Post by Neil Pik » Tue, 20 May 1997 04:00:00


Jim,

I'd need the other table definitions, and any required test data to re-create
it.


 Protech Computing Ltd (MS Solution Provider)
 Using Virtual Access 4.00 build 213a (32-bit) on NT 4.0 SP3

 
 
 

1. INSTEAD OF INSERT - inserted IDENTITY COLUMN = 0!

When I have an INSTEAD OF INSERT Trigger on a Table with an identity
column, the inserted table returns 0 for the identity column values.  

SQL BOL says something about needing to have SET IDENTITY_INSERT
Accstatuss ON for the table.  I can't retrieve the values from the
identity column!

--My table
CREATE TABLE [ACCSTATUSs] (
        [AccStatusID] [int] IDENTITY (1, 1) NOT NULL ,
        [AccStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [CatDate] [datetime] NULL ,
        [DateCreated] [datetime] NULL ,
        [Cataloguer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
        [CreatedBy] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [MyText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        CONSTRAINT [aaaaaACCSTATUSs_PK] PRIMARY KEY  NONCLUSTERED
        (
                [AccStatusID]
        ) WITH  FILLFACTOR = 90  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

--my trigger
create    Trigger AccStatuss_AuditInsert
On AccStatuss
INSTEAD OF Insert
As

print 'the inserted table has irrelevant identity col values i.e. zero'
select accstatus from inserted i

go

--When inserting into the table
INSERT AccStatuss (MYTEXT) VALUES ('HELLO')

--The result is :

accstatusID
-----------
0

(1 row(s) affected)

http://www.psam.ru.ac.za
Monitoring Transparency and Accountability in the Public Service, South
Africa

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. Slow SQL with lots of sub SELECT() statements to get Field values

3. Insert Into, IDENTITY column, and manual inserts

4. Password encrypt

5. Inserting values in the IDENTITY columns using DTS

6. Two ways to quote your query ?1

7. Insert value to identity column

8. US-IL-CHICAGO-TECHNICAL DESIGNER-OPTIMA PARTNERS

9. Getting the value of an IDENTITY column after an INSERT query

10. inserting explicit values in the identity column

11. Retrieve Just Inserted value in IDENTITY column

12. : inserting values into IDENTITY column

13. How to get Identity column values for newly inserted row