SQL Server Re-Insert/Update primary key value after a mistake (restoration) [duplicate]

zte4gxcn 于 2023-11-16 发布在其他

关注(0)|答案(2)|浏览(142)

This question already has answers here:

How can I insert identity manually? (4 answers)
Closed 17 hours ago.

I encounter a problem in SQL Server.

We have a table Country with N_Country as its primary key. Please note the table have like 10 columns but I am only showing these two 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 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:

I create a new row (N = 6, Name = France) and then I

UPDATE Country
 SET N_Country = 3 
 WHERE N_Country = 6

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.

sql-server

来源：https://stackoverflow.com/questions/77486854/re-insert-update-primary-key-value-after-a-mistake-restoration

2条答案

按热度按时间

7gcisfzg1#

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.

赞(0）回复(0）举报 2023-11-16

wooyq4lh2#

You can insert just the missing records using this technique.

You need to make sure your INDENTITY_INSERT statements match - in your example, you set it ON for Country , but you switch it OFF for aTable . This would leave identity insert open on your Country table.

SET IDENTITY_INSERT Country ON
INSERT INTO Country (N_Country, Name) VALUES (3, 'France') 
SET IDENTITY_INSERT Country OFF

Tested with schema

CREATE TABLE Country(
  N_Country INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(100)
)

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Re-Insert/Update primary key value after a mistake (restoration) [duplicate]

2条答案

相关问题

热门标签

最新问答