如何在SQL中获取主键的身份 [英] How to get identity for primary key in sql

问题描述

大家好，

我有一个表作为emp(empid主键，标识，sal，dept).
我插入了10个值，例如

Hi all,

I have a table as emp(empid primary key,identity,sal,dept).
I inserted 10 values like

empid  sal dept

1     2000  3
2     3000  2
3     500   1
.     ...   .
.     ...   .
.     ...   .
10    2000  3

现在，我将empid从5删除为10.

现在，我插入的新值是从11开始的......
但是这里需要将empid id设为6 ........

我怎么能克服这个.........请任何人帮助我
预先感谢..........

Now I deleted empid from 5 to 10.

Now I am inserting new values it''s empid takes from 11.....
but here will need to takes empid id as 6........

How can I over come this .........please any body help to me
Thanks in advance..........

推荐答案

如果SET IDENTITY_INSERT为ON，则尝试删除中间行数据，如果尝试再次删除要添加新行，新行将花费max(id)+1而不是中间行值.

为了手动插入中间行，您必须通过将SET IDENTITY_INSERT设置为off来完成.

SET IDENTITY_INSERT OFF
插入emp(主键，标识，sal，dept)值(6个，您的值)中.

然后再次尝试输入IDENTITY_INSERT.那么它将继续以id为
max(id)+1.

设置IDENTITY_INSERT ON

If SET IDENTITY_INSERT is ON then if you try to delete the middle rows data and again if you try to add the new row it will take max(id)+1 for the new row not the middle rows values.

Inorder to insert middle rows manually you have to do it with by setting SET IDENTITY_INSERT to off.

SET IDENTITY_INSERT OFF
insert into emp(empid primary key,identity,sal,dept) values (6, your values).

then again try to on the IDENTITY_INSERT. then it will continue with taking id as
max(id)+1.

SET IDENTITY_INSERT ON

<pre lang="SQL">
SET IDENTITY_INSERT OFF
insert into emp(empid primary key,identity,sal,dept) values (6, your values).
SET IDENTITY_INSERT ON

嗨 您无法获取已删除的ID.因为您的主键将自动生成.

如果要插入具有已删除ID的记录，则必须

Hi You cannot get deleted id. because ur primary key will be auto generated.

if you want to insert record with deleted id then you have to

SET IDENTITY_INSERT OFF
insert into emp(empid primary key,identity,sal,dept) values (6, your values).
SET IDENTITY_INSERT ON

尝试阅读本文:


[ SQL如何发现序列中的漏洞]

使用最后一个sql获取第一个空闲编号，然后在插入查询中使用该编号.将值插入标识列中时，请记住使用IDENTITY_INSERT.

问候
约阿希姆(Joachim)

Hi,

Try read this article:


[SQL-How-to-find-holes-in-sequences]

Using the last sql to get the first free number and then use this number in the insert query. Remember to use the IDENTITY_INSERT when inserting values into an identity column.

Regards
Joachim

这篇关于如何在SQL中获取主键的身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！