-2

Possible Duplicate:
Reset AutoIncrement in SqlServer after Delete

I'm having an annoying issue with an Identity auto-increment column value within an Sql Server 2008 database. When I delete a row from my table (or delete all rows), when I add a new entry it's incremented from whichever value was inserted into a previous record, regardless as to whether that record still exists.

I was wondering if anyone knows how to get it so that the IDENTITY column will auto-increment from whatever the previous record that EXISTS is.

My code looks like this:

    CREATE TABLE Product
    (
            Product_Barcode INT NOT NULL PRIMARY KEY IDENTITY,
            Product_Description VARCHAR(255),
            Product_Name VARCHAR(255),
            Product_Supplier VARCHAR(255) FOREIGN KEY REFERENCES Supplier(Supplier_Name),
            Product_Size VARCHAR(255),
            Product_Weight FLOAT
    );
Community
  • 1
  • 1
Daniel Lane
  • 2,575
  • 2
  • 18
  • 33
  • 2
    That's what the identity column does; why does it matter what the value is? It's just a key for querying or joining tables. – LittleBobbyTables - Au Revoir Nov 27 '12 at 15:09
  • @LittleBobbyTables: it matters because when creating a record for product, I also create a record for an inventory table that is linked by a foreign key to barcode. I do this programmatically by getting the last entry in product barcode and adding+1 to the value, unless there are no entries in which case I tell it to start at 1. It creates the problem where if I delete entries, the i.d will continue being incremented and my code will be pointing at the wrong product, and I can't exactly query an empty table for the previous barcode value =/ – Daniel Lane Nov 27 '12 at 15:19
  • 1
    @Termin8tor - ... or you could use `SCOPE_IDENTITY()` to get the just-inserted value from the Product table. – LittleBobbyTables - Au Revoir Nov 27 '12 at 15:20
  • Ah thanks buddy, it's one of those where a second opinion can help a lot, that's actually what I need. Thanks a lot! – Daniel Lane Nov 27 '12 at 15:25

1 Answers1

1

Typically you wouldn't want this because new and past records can share IDs. If you have referential constraints, this might cause confusion.

The only way I know how to do it though, is by inserting using a subquery eg.

INSERT INTO Product (Product_Barcode, Product_Description) VALUES (SELECT MAX(Product_Barcode) + 1 FROM Product, "Any description here");
deemen
  • 118
  • 6
  • *Typically you wouldn't want this* Agreed, it is unsafe - especially if there is concurrent access. A safer approach is using `SCOPE_IDENTITY()` after your initial `INSERT` as LittleBobbyTables suggested. – Leigh Nov 29 '12 at 04:27