重新整理主键
来源:互联网 发布:mac 查看node安装路径 编辑:程序博客网 时间:2024/04/29 12:30
----------------------------------------------------------------------
-- 版权: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
-- 版权: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
- 重新整理主键
- 重新整理
- 重新整理
- 重新整理思绪
- 重新整理索引信息
- 整理思路,重新计划
- 重新整理反射--基础
- 重新整理反射--特性
- 操作系统重新整理
- 重新整理-------对象系列.
- mysql 自增主键重新排序
- mysql主键id重新设置从一开始
- mysql让主键id重新排序
- J2ME电话薄源代码重新整理
- IO部分细节重新整理
- 光类型 重新整理总结
- block系列重新整理1
- block系列重新整理2
- Android 实现聊天界面
- MS SQLServer 数据库日志文件收缩
- 宏控制打印信息输出
- 中华万年历——开启个人数据存储新纪元
- Delphi XE5 Android 调用手机震动
- 重新整理主键
- win8.1 64bit 小米开发 delphi xe5
- 各种常规排序算法总结
- 专访:InMobi全球CEO Naveen Tewari
- 输入字符串,要求打印出所有字符排列的可能性
- java运行带有main方法的jar文件
- 黑马程序员_源自梦想 GUI
- jsonp和java联合使用解决跨域问题
- JEECG ——11月份版本即将发布功能点