SQL Server导入数据时标识列存在的问题解决办法

--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'

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