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

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 */



Fri, 05 Nov 1999 03:00:00 GMT
 INSERT INTO with SELECT DISTINCT causes a zero value to be inserted into an IDENTITY column

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



Fri, 05 Nov 1999 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

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

2. Insert Into, IDENTITY column, and manual inserts

3. Inserting values in the IDENTITY columns using DTS

4. Insert value to identity column

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

6. inserting explicit values in the identity column

7. Retrieve Just Inserted value in IDENTITY column

8. : inserting values into IDENTITY column

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

10. INSERT stored procedure with identity column return value

11. Inserting select and a corresponding value from an identity field in another table

12. INSTEAD OF INSERT trigger on view with identity column requires value


 
Powered by phpBB® Forum Software