Microsoft KB Archive/155795

= BUG: Table Populated With Zeros in the Identity Column =

Article ID: 155795

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q155795



BUG #: Windows NT: 15764 (6.50) 15661 (6.00)



SYMPTOMS
When you Insert into a table with an identity column using either

  Select with "order by" and an outer join

-or-

Select with distinct and an outer join

the table becomes populated with zeros in the identity column.



RESOLUTION
To work around this problem, you can first do a select into a temp table, and then do the insert from the temp table. For example, the following code reproduces the problem: create table tbl(i int identity(1,1), id varchar(80))

Insert into tbl(id) select authors.au_id from authors ,titleauthor where authors.au_id *= titleauthor.au_id order by authors.au_lname The workaround would be: select authors.au_id into #temp from authors ,titleauthor where authors.au_id *= titleauthor.au_id order by authors.au_lname Insert into tbl(id) select * from #temp drop table #temp



STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.



MORE INFORMATION
The left outer join syntax also results in the problem mentioned above. The "order by" clause must be present and the column "ordered by" must not be in Select list. The following is a sample query:

Insert into tabl(id) select authors.au_id from authors ,titleauthor where authors.au_id *= titleauthor.au_id order by authors.au_lname go  select * from tbl go

Keywords: kbbug kbprogramming KB155795

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.