如何在SQL中获取主键的身份

本文介绍了如何在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