Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Re-Insert/Update primary key value after a mistake (restoration)

I encounter a problem on MSSQL.

We have a table named "Country" which is as follow:
(Primary key is N_Country)
(Please note the table have like 10 columns but I am only showing these two ones for the example)

N_Country Name
1 United Kingdom
2 España
3 France
4 Deutschland
5 Italia

However, we do not know why, but the 3rd row (France) got deleted. Now, we only have as primary keys 1/2/4/5 and no longer 3.
I can not add a row into this table as it would make a country with primary key 6, as I would need to update all my other tables which had "N = 3" to "N = 6", which is a lot of work (and I "do not know all the tables" that would need that update).

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

The "fastest/best" two solutions I see are:

1- I create a new row (N = 6, Name = France) and then I UPDATE Country and SET N_Country = 3 WHERE N_Country = 6

2- I directly do INSERT INTO Country (N_Country, Name) VALUES (3, 'France')

Both should be "safe" as I am just restoring a deleted value. I never update primary keys (as it is a really bad idea) but here I am stuck with the error "IDENTITY_INSERT is defined to OFF".

I saw from related post the SET IDENTITY_INSERT aTable ON

I would like to know if I can use something like this?

SET IDENTITY_INSERT Country ON

GO

-- Attempt to insert an explicit ID value of 3

INSERT INTO Country (N_Country, Name) VALUES (3, 'France')

GO

SET IDENTITY_INSERT aTable OFF

GO

And also if you think this is "safe"? (I guess so) – But if my table Country has 10 columns, should I "INSERT" all 10 columns or can I just INSERT the two main ones, and then update later?

Thank you for your help.
Edit1: style format

>Solution :

In case you want to insert a specific value on an identity column then first

SET IDENTITY_INSERT <schema>.<table> ON;

This allows with a follow up insert statement to insert a wanted value for the identity column.

Very important afterwards:

SET IDENTITY_INSERT <schema>.<table> OFF;

Only one ON state can exist across all tables in a database at a time.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading