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).
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.