重新整理主键

----------------------------------------------------------------------
-- 版权：2013
-- 时间：2013-03-15
-- 用途：当表PayInfo、ProjectInfo主键不是从1开始或者不连续,重新整理主键
----------------------------------------------------------------------


USE NBFinancialDB


DECLARE @StartID INT -- 开始主键
DECLARE @EndID INT -- 结束主键
DECLARE @Counts INT -- 记录总数


-- 整理ProjectInfo
-- 查询信息
SELECT @StartID=MIN(ProjectID),@EndID=MAX(ProjectID) FROM ProjectInfo
IF @StartID IS NULL or @EndID IS NULL
BEGIN
SET @StartID=0
SET @EndID=0
END


SELECT @Counts=COUNT(ProjectID) FROM ProjectInfo
IF @Counts IS NULL
BEGIN
SET @Counts=0
END


-- 主键异常
IF @EndID!=@Counts OR @StartID>1
BEGIN
-- 开始事务
SET XACT_ABORT ON;
BEGIN TRAN

-- 建临时表
SELECT ROW_NUMBER() OVER(ORDER BY ProjectID ASC) AS ID,* INTO #tmpTable FROM ProjectInfo
DECLARE @i INT
DECLARE @ProjectID INT
SET @i=1

-- 开启插入
SET IDENTITY_INSERT ProjectInfo ON;

-- 循环处理
WHILE @i<=@Counts
BEGIN
SELECT @ProjectID=ProjectID FROM #tmpTable WHERE ID=@i
IF @ProjectID<>@i
BEGIN
-- 修改主键
DELETE ProjectInfo WHERE ProjectID=@ProjectID
INSERT ProjectInfo(ProjectID,ProjectName,TotalAmount,ProjectLevel,ProjectNumLevel,SalesmanID,MaintainerID,Manager,Email,ProjectDomain,EndDate,InputDate,FinishDate,[Status],Remark)
SELECT @i,ProjectName,TotalAmount,ProjectLevel,ProjectNumLevel,SalesmanID,MaintainerID,Manager,Email,ProjectDomain,EndDate,InputDate,FinishDate,[Status],Remark 
FROM #tmpTable WHERE ProjectID=@ProjectID
-- 修改付款
UPDATE PayInfo SET ProjectID=@i WHERE ProjectID=@ProjectID
END
SET @i=@i+1
END

-- 关闭插入
SET IDENTITY_INSERT ProjectInfo OFF;

-- 删临时表
DROP TABLE #tmpTable

-- 定义种子
DBCC CHECKIDENT(ProjectInfo,reseed,@Counts)

-- 提交事务
COMMIT TRAN
END


-- 整理PayInfo
-- 查询信息
SELECT @StartID=MIN(PayID),@EndID=MAX(PayID) FROM dbo.PayInfo
IF @StartID IS NULL or @EndID IS NULL
BEGIN
SET @StartID=0
SET @EndID=0
END


SELECT @Counts=COUNT(PayID) FROM dbo.PayInfo
IF @Counts IS NULL
BEGIN
SET @Counts=0
END


-- 主键异常
IF @EndID!=@Counts OR @StartID>1
BEGIN
-- 开始事务
SET XACT_ABORT ON;
BEGIN TRAN

-- 建临时表
SELECT ROW_NUMBER() OVER(ORDER BY PayID ASC) AS ID,* INTO #tmpTable2 FROM PayInfo
DECLARE @PayID INT
SET @i=1

-- 开启插入
SET IDENTITY_INSERT PayInfo ON;

-- 循环处理
WHILE @i<=@Counts
BEGIN
SELECT @PayID=PayID FROM #tmpTable2 WHERE ID=@i
IF @PayID<>@i
BEGIN
-- 修改主键
DELETE PayInfo WHERE PayID=@PayID
INSERT PayInfo(PayID, ProjectID, PayAccounts, PayAmount, Debt, InputDate, Remark)
SELECT @i, ProjectID, PayAccounts, PayAmount, Debt, InputDate, Remark 
FROM #tmpTable2 WHERE PayID=@PayID
END
SET @i=@i+1
END

-- 关闭插入
SET IDENTITY_INSERT PayInfo OFF;

-- 删临时表
DROP TABLE #tmpTable2

-- 定义种子
DBCC CHECKIDENT(PayInfo,reseed,@Counts)

-- 提交事务
COMMIT TRAN
END