Can someone explain this result?
I have a table in SQL 05, lets call it [Pool] with the columns [PoolID] int Identity(1,1) not null,
[AccountID] nchar(8) PK not null, [A], [B], [C]...
This was created by someone else some time ago and relates to other tables by [PoolID].
There are 670 records in this table, and [PoolID] is the same as the record number.
So I needed to append new records to this table - I had a table, call it [New], with 661 records to append, and I knew that 606 of them were duplicate [AccountID]s. So what I wanted was to add the 55 non-duplicate records and have their [PoolID] values increment from 671 to 725.
I wrote my query like this:
Insert Into [Pool] ([AccountID], [A], [B], [C]...)
Select [AccountID], [A], [B], [C]...
Where (Not Exists (Select [AccountID] From [Pool] Where ([New].[AccountID] = [AccountID])))
So the 55 non-duplicate records were appended to [Pool] like expected, but here's the strange part - the [PoolID] values are 1332 to 1386?
I was expecting the [PoolID] column to act like an Access AutoNumber column - why did the [PoolID] jump from 670 to 1332?