发现MSSQL导入数据时标识列存在的问题，已解决！

(2008/1/11已更新，附上解决方案，可以自动修复标识列的编码规则）
最近在合并异地SQL数据的时候，发现MSSQL的标识列在合并数据时，可以启用标识插入也可以不启用标识插入，两种方法都可能存在一些潜在的问题，最严重的是导入操作改变了目的表的标识列编号规则。

场景：A地的A表，B地有B表，数据结构除了标误列的种子不一样外其它完全一样：
A(ID IDENTITY (1, 2), Content varchar(50)) 生成奇数主键ID
B(ID IDENTITY (2, 2), Content varchar(50)) 生成偶数主键ID

-- A地的A表
CREATE   TABLE  dbo.A
    (
    ID  int   NOT   NULL   IDENTITY  ( 1 ,  2 ),
    Content  varchar ( 50 )  NOT   NULL
    )   ON   [ PRIMARY ]
GO
ALTER   TABLE  dbo.A  ADD   CONSTRAINT
    PK_A  PRIMARY   KEY   CLUSTERED  
    (
    ID
    )  ON   [ PRIMARY ]
-- 在A表中插入两条数据：预先插入A表两条数据
INSERT   INTO   [ dbo ] . [ A ]  ( [ Content ] )  VALUES  ( ' A1 ' )  -- 生成1, 'A1'
INSERT   INTO   [ dbo ] . [ A ]  ( [ Content ] )  VALUES  ( ' A3 ' )  -- 生成3, 'A3'

-- B地的B表
CREATE   TABLE  dbo.B
    (
    ID  int   NOT   NULL   IDENTITY  ( 2 ,  2 ),
    Content  varchar ( 50 )  NOT   NULL
    )   ON   [ PRIMARY ]
GO
ALTER   TABLE  dbo.B  ADD   CONSTRAINT
    PK_B  PRIMARY   KEY   CLUSTERED  
    (
    ID
    )  ON   [ PRIMARY ]

-- 在B表中插入1条数据：预先插入B表1表数据
INSERT   INTO   [ dbo ] . [ B ]  ( [ Content ] )  VALUES  ( ' B2 ' )  -- 生成2, 'B2'

现在要将A表数据导入到B表，问题出现了：
1. 不启用标识插入的问题：
    不启用标识，B表会增加两条数据
        4,'A1'
        6,'A3'
    造成同一数据在两地数据库表中的不一致情况，以及无法判断或者找出哪些数据已合并，哪些数据已修改或已删除，哪些数据重复导入等等问题。
2. 启用标识插入的问题：
    启用标识后，B表会增加两条数据
        1,'A1'
        3,'A3'
     用户最不能接受的错误出现了，B表录入的新数据是
        5, '从A表导入两条数据后录入B表的新数据1'
        7, '从A表导入两条数据后录入B表的新数据2'
    导入合并操作改变了B表的标识列编号规则。


2008-1-11，今天终于搞定了，一个存储过程完成解决，自动修复标识列的编码规则。

上例中，调用dbo.sp_RepairIDENTITY 'B', 'ID' 即可，执行结果如下：
当前最大值：3
标识种子：2
标识增量：2
当前标识值：3
需设新标识值：4
检查标识信息: 当前标识值 '3'，当前列值 '4'。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
结果：已修复标识，当前标识值重设为4

代码如下：

CREATE   PROCEDURE  dbo.sp_RepairIDENTITY 
( 
@Tablename   nvarchar ( 255 ),  -- 表名 
@RowID_Name   nvarchar ( 20 )  -- 标识列名 
) 
as  
SET  NOCOUNT  ON  
-- Select @TableName=N'B',@RowID_Name=N'ID' 

-- 获取最大标识值MaxID 
Declare   @sql   nvarchar ( 4000 ),  @MaxID   bigint  
Set   @sql   =  N ' Select @MaxID =Max( '   +   @RowID_Name   +  N ' ) from  '   +   @TableName  
Exec  sp_Executesql  @sql , N ' @MaxID bigint output ' , @MaxID  output 
-- print @MaxID 

Declare   @IDENT_CURRENT   bigint ,  @NewIDentity   bigint ,  @IDENT_SEED   int ,  @IDENT_INCR   int  
Select   @IDENT_SEED   =   IDENT_SEED ( @TableName ),  -- 标识种子 
@IDENT_INCR   =   IDENT_INCR ( @TableName ),  -- 标识增量 
@IDENT_CURRENT   =  IDENT_CURRENT( @TableName ),  -- 当前标识值 
@NewIDentity   =   @MaxID   -   @MaxID   %   cast ( IDENT_SEED ( @TableName )  as   int )  +   IDENT_INCR ( @TableName )  -- 需要设置的新标识值 

Print   ' 当前最大值： '   +   Cast ( @MaxID   as   nvarchar ( 20 )) 
Print   ' 标识种子： '   +   Cast ( @IDENT_SEED   as   nvarchar ( 20 )) 
Print   ' 标识增量： '   +   Cast ( @IDENT_INCR   as   nvarchar ( 20 )) 
Print   ' 当前标识值： '   +   Cast ( @IDENT_CURRENT   as   nvarchar ( 20 )) 
Print   ' 需设新标识值： '   +   Cast ( @NewIDentity   as   nvarchar ( 20 )) 

-- ----关键代码：启用标识导入数据或手工执行过DBCC CHECKIDENT('table_name',RESEED new_reseed_value)就会造成编码规则改变，所以要修复编码规则 
if ( @IDENT_CURRENT   <   @MaxID   or   @IDENT_CURRENT   %   @IDENT_SEED   > 0 ) 
begin  
     DBCC  CHECKIDENT ( @TableName , RESEED,  @NewIDentity ) 
     print   ' 结果：已修复标识，当前标识值重设为 '   +   Cast ( @NewIDentity   as   nvarchar ( 20 )) 
end  
else  
begin  
     Print   ' 结果：不需要重设标识列。 '  
end  

SET  NOCOUNT  OFF  

简单 2008-01-10 15:12 发表评论

转载于:https://www.cnblogs.com/guodapeng/archive/2008/05/05/1183499.html