Post reply

Missing Row and ID can it be inserted again?

  • February 2, 2007 at 7:42 am

    #153415

    I have a table that has a missing row. This table has an Identity seed and I need to recreate this row. How can I do this?

    Can I just insert into the table with that missing ID specified as the value for that column? Or do I need to create a new table?

  • February 2, 2007 at 8:14 am

    #687436

    Firstly you'll need to make sure that no changes are currently being made to your table. You could then copy the do the following:

    --Copy data to temp

    SELECT (idCol, col1, col2, col3)

    INTO #tmpBackup

    FROM table

    WHERE ID > missingID

    DELETE FROM table

    WHERE ID > missingID

    GO

    --RESEED the ID column

    DBCC CHECKIDENT (table, RESEED, missingID -1)

    GO

    --Insert missing values

    INSERT INTO table (col1, col2, col3)

    VALUES (val1, val2, val3)

    -- Copy Data back to table

    INSERT INTO table(col1, col2, col3)

    SELECT col1, col2, col3

    FROM #tmpBackup

    ORDER BY ID

    I think that this should work . There are probably better ways of doing this.....

    Ade


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • February 2, 2007 at 8:16 am

    #687438

    Well I fixed it but not the optimal way. I found the ID that was missing. Renamed the table, disabled ID seed. Inserted the value in the ID column, Renabled the ID seed and set the Seed Start value to the highest current ID and then renamed the table the original name. Any one have a better way?

  • February 2, 2007 at 9:03 am

    #687461

    what about SET IDENTITY_INSERT?  Sorry about length of this post, but BOL says it pretty well

    Description from SQL 2000 Books Online

    Allows explicit values to be inserted into the identity column of a table.

    At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

    If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

    The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

    Example from SQL 2000 Books Online

    This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

    -- Create products table.CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))GO-- Inserting values into products table.INSERT INTO products (product) VALUES ('screwdriver')INSERT INTO products (product) VALUES ('hammer')INSERT INTO products (product) VALUES ('saw')INSERT INTO products (product) VALUES ('shovel')GO-- Create a gap in the identity values.DELETE products WHERE product = 'saw'GOSELECT * FROM productsGO-- Attempt to insert an explicit ID value of 3;-- should return a warning.INSERT INTO products (id, product) VALUES(3, 'garden shovel')GO-- SET IDENTITY_INSERT to ON.SET IDENTITY_INSERT products ONGO-- Attempt to insert an explicit ID value of 3INSERT INTO products (id, product) VALUES(3, 'garden shovel').GOSELECT * FROM productsGO-- Drop products table.DROP TABLE productsGO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply