Post reply

How To Re-Insert a Row with Identity Value

  • November 7, 2021 at 10:59 pm

    #3948846

    I have accidentally deleted a record which originally looked like this:

    re-insert1

     

    But now looks like the following:

    re-insert

    As you can see row, 515 13 DriverId has been deleted.

    I want to re-insert the row with ColumnID 515, however because ColumnID is an Identity value i.e. Auto-Increments I can only enter a row with ColumnID 519.

    I was thinking that the only way to re-enter a row with ColumnID 515 would be to disable Identity on ColumnID field and enter a row with ColumnID 515, however I'm sure there is a better of way of doing it. Can someone let me confirm if I have to remove Identity on ColumnID to enter an ID of 515?

    Thanks

     

  • November 8, 2021 at 5:53 am

    #3948911

    Use IDENTITY_INSERT:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15

    SET IDENTITY_INSERT [YourTableName] ON;

    INSERT [YourTableName](ColumnID, ID, ColumnName)
    SELECT 515, 13, 'DriverID';

    SET IDENTITY_INSERT [YourTableName] OFF;

    Eddie Wuerch
    MCM: SQL

  • November 12, 2021 at 1:39 pm

    #3952396

    Additionally, if you need to find out more information about your identity values you can use DBCC CheckIdent.

    Use AdventureWorks;
    Go

    DBCC CheckIdent ('Person.AddressType');
    Go

    Richard

    Just learning about databases and Sql Server since 1998.

     

  • November 20, 2021 at 11:48 am

    #3955116

    I would also be asking WHY you need to re-insert it with the correct ColumnID record.

    If ID contiguity (i.e. no breaks) is an audit requirement then you have just failed the audit because the whole point of the audit check is that you shouldn't be able to delete a record.

    If the ID is required because it is a foreign key on another table then you don't have referential integrity configured properly as you should not have been able to delete the record if there are other tables relying on it.

    If it is neither of these then I would suggest that this is a case where the ColumID should be redundant as you have a perfectly valid business key of ID/ColumnName.

    Also the naming convention looks wierd. If you have a column called ID, that by convention is the INT IDENTITY column and is also normally the Primary Key and the CLUSTERED index.  If I didn't know your system I would automatically try to JOIN to this table on the ID field and then spend hours wondering why I have too many records 🙂

    I'm not saying its wrong, just wierd...

    • This reply was modified 2 years, 10 months ago by  aaron.reese.

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

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