重新整理主键

----------------------------------------------------------------------
-- 版权: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
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值