Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unexpected result of appending records to a table in SQL 05 Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 5:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 6, 2013 10:18 AM
Points: 5, Visits: 49
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]...
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?
Post #1444193
Posted Thursday, April 18, 2013 6:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:40 PM
Points: 1,787, Visits: 5,692
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1444195
    Posted Thursday, April 18, 2013 6:16 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: Administrators
    Last Login: Yesterday @ 9:02 PM
    Points: 33,153, Visits: 15,284
    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.







    Follow me on Twitter: @way0utwest

    Forum Etiquette: How to post data/code on a forum to get the best help
    Post #1444197
    Posted Thursday, April 18, 2013 6:16 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, May 6, 2013 10:18 AM
    Points: 5, Visits: 49
    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?
    Post #1444198
    Posted Thursday, April 18, 2013 6:31 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, May 6, 2013 10:18 AM
    Points: 5, Visits: 49
    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...
    Post #1444200
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse