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

>= 0),

    Priority                      tinyint               null    
        constraint CKC_PRIORITY_WORKCENT check (Priority >= 0),
    SupervisorStopWorkCode        tinyint               null    ,
    TotalQuantityCompleted        T_WholeQuantity       not null
        default 0
(TotalQuantityCompleted >= 0),
    CurrentQuantityToMake         T_WholeQuantity       not null
        default 0
(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
    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

 THIS STATEMENT CAUSES the value of zero to be inserted into column
 I believe that this is a Microsoft BUG!!!
INSERT INTO WorkCenterPerformance
 ( WorkCenterID
  ,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            =
   LEFT OUTER JOIN      BookSpecOperations      BSO     ON BS.BookSpecPartID            =
   LEFT OUTER JOIN      SpecOperWorkCenter      SOW     ON BSO.SpecOperWorkCenterID     =
      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


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

 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 


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