SqlServer数据维护

现有两个表：Code和CodeCategory

Code表：

CodeCategory表：

现要把Code表中的数据如实维护一份数据，但是要设PlantID字段值为2，而ID要按规则自增并且要与PlantID=1的数据ID 留出一段空间以防以后增添，摸索半天想到如下办法：

 

/*
基础数据维护sql脚本
根据CodeCategory表中的数据维护Code表中的多工厂数据
*/
BEGIN TRANSACTION
BEGIN
-- 声明变量
DECLARE @CodeCategoryCount INT;--CodeCategory的数量
DECLARE @Loop INT; -- 循环标记变量
DECLARE @PlantID INT;
DECLARE @ReservedSpace INT; -- 新插入的数据ID与原有PlantID为1的ID的预留空间
DECLARE @OldID VARCHAR(10);
DECLARE @CurrentID VARCHAR(10);
DECLARE @RESULTID VARCHAR(10); -- 获取实际要插入的ID
DECLARE @Flag VARCHAR(10); --用于标记ID值的前两位是否改变
DECLARE @PlusCount int; -- 每次循环的增加量
SELECT @CodeCategoryCount=COUNT(*) from [TEST].[dbo].[Code] WHERE PlantID=1;

-- 在当前库创建一张新表用于存储PlantID=2的所有数据，创建前先检查该表是否存在，如果存在则先要删除，否则会出错
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'CodeHelp')
DROP TABLE CodeHelp
USE [TEST]
CREATE TABLE [dbo].[CodeHelp](
[ID] [varchar](10) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[CodeCategoryID] [varchar](50) NOT NULL,
[Order] [int] NOT NULL,
[Extend] [varchar](100) NULL,
[IssueTypes] [varchar](100) NULL,
[Comment] [nvarchar](500) NOT NULL,
[IsValid] [bit] NOT NULL,
[PlantID] [int] NULL,
)

SET @Loop=1;
SET @PlusCount=0;
WHILE @Loop < @CodeCategoryCount
BEGIN
SET @PlantID=1;
SET @ReservedSpace=30;-- 预留30个自然数的空间
-- 查出Code表里所有PlantID=1的ID值，用以计算PlantID=2的ID值
SELECT @OldID=ID FROM (select *,ROW_NUMBER()over(order by ID) nb from [TEST].[dbo].[Code] WHERE PlantID=@PlantID)TB
WHERE nb=@Loop
IF SUBSTRING(@OldID,1,2)!=SUBSTRING(@Flag,1,2)
BEGIN
-- 如果ID值的前两位不同则将增加量重新调整为0
SET @PlusCount=0;
END
SELECT @CurrentID=CAST(SUBSTRING(CAST(max(ID)AS VARCHAR(10)),1,2) AS VARCHAR(10))+CAST((SUBSTRING(CAST(max(ID) AS VARCHAR(10)),3,LEN(MAX(ID))-2)+@ReservedSpace) AS VARCHAR(10)) FROM [TEST].[dbo].[Code] WHERE ID like CAST(SUBSTRING(@OldID,1,2) as varchar(10))+'%'
SET @Flag = @OldID;
-- 在PlantID=1的ID值的基础上计算出PlantID=2的初始值（即由PlantID=1的最大ID值加上预留空间，得出）
-- 由以上两ID值和循环索引，得出可插入的ID值
SELECT @RESULTID=CAST(SUBSTRING(@CurrentID,1,2) AS VARCHAR(10))+CAST((SUBSTRING(@CurrentID,3,LEN(@CurrentID)-2)+@PlusCount)AS VARCHAR(10))
--先将数据保存到新建的辅助表
INSERT INTO [TEST].[dbo].[CodeHelp]
-- 查询出要插入的单条数据
SELECT @RESULTID AS ID,Name,CodeCategoryID,[Order],Extend,IssueTypes,Comment,IsValid,PlantID=2 FROM (
select *,ROW_NUMBER()over(order by ID) nb
from [TEST].[dbo].[Code] WHERE PlantID=@PlantID)TB
WHERE nb=@Loop

/*查询以确认ID值是否正确*/
--select @OldID AS OldID,@CurrentID AS CURID,@Loop as loops,@RESULTID AS RESULT,@PlusCount as '增加量'

SET @Loop=@Loop+1
SET @PlusCount=@PlusCount+1
END

--将保存在辅助表中的数据插入到Code表中
INSERT INTO [TEST].[dbo].[Code] SELECT * FROM [TEST].[dbo].[CodeHelp]

--删除辅助表
DROP TABLE [TEST].[dbo].[CodeHelp]

END

IF @@ERROR>0
BEGIN
RAISERROR('出错了',16,1)
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION

至此，这个事情算是解决了吧，好累！