Unexpected result of appending records to a table in SQL 05

Question

Unexpected result of appending records to a table in SQL 05

msheeha2

SSC Veteran

Points: 299
More actions
April 18, 2013 at 5:32 pm

#98588

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], , [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], , [C]...)
Select [AccountID], [A], , [C]...
From [New]
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?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

It's impossible to say for sure, unless you restore a backup, but most likely is that the table has previously contained up to 1331 and then had some rows deleted.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

There is no such thing as a record number in SQL Server.

You can set an identity property for a column, and it will autoincrement based on settings, but there is no guarantee for these to be contiguous or gaps to be filled. You can get the current identity value with the ident_current() function (http://msdn.microsoft.com/en-us/library/ms175098.aspx)

If you want to insert specific values, you would need to use SET IDENTITY_INSERT and specify the values in your insert statement.

msheeha2 SSC Veteran Points: 299 More actions · Answer 3

Well, I did inherit the table...

I've been looking for a way to insert the 55 records and have [PoolID] start at the next available number (671). I found instructions for using SET IDENTITY_INSERT but it looks like I would have to add each row explicitly with the numbers 671 to 725. Is there a way around this?

msheeha2 SSC Veteran Points: 299 More actions · Answer 4

Whoops, I should refresh before I reply...

The way I understand it, there never should have been records deleted from this table, and it's a awfully big coincidence that the gap in [PoolID] values is the same as the number of records in my [New] table (606 dupes + 55 non-dupes). I was thinking I caused the gap somehow with my insert query...