将数据以某个单位进行拆分

将数据以2000为单位对表数据进行拆分

 

创建表和插入数据

 

CREATE TABLE [dbo].[testtb](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Sonum] [varchar](10) NULL,
    [ItemNum] [varchar](30) NULL,
    [ItemQty] [int] NULL,
    [SoArea] [varchar](30) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[testtb] ADD  CONSTRAINT [DF_testtb_Sonum]  DEFAULT ('') FOR [Sonum]
GO

ALTER TABLE [dbo].[testtb] ADD  CONSTRAINT [DF_testtb_ItemNum]  DEFAULT ('') FOR [ItemNum]
GO

ALTER TABLE [dbo].[testtb] ADD  CONSTRAINT [DF_testtb_ItemQty]  DEFAULT ((0)) FOR [ItemQty]
GO

ALTER TABLE [dbo].[testtb] ADD  CONSTRAINT [DF_testtb_SoArea]  DEFAULT ('') FOR [SoArea]
GO



CREATE TABLE [dbo].[testtb2](
    [id] [int] NULL,
    [Sonum] [varchar](10) NULL,
    [ItemNum] [varchar](30) NULL,
    [ItemQty] [int] NULL,
    [SoArea] [varchar](30) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[testtb2] ADD  CONSTRAINT [DF_testtb2_id]  DEFAULT ((0)) FOR [id]
GO

ALTER TABLE [dbo].[testtb2] ADD  CONSTRAINT [DF_testtb2_Sonum]  DEFAULT ('') FOR [Sonum]
GO

ALTER TABLE [dbo].[testtb2] ADD  CONSTRAINT [DF_testtb2_ItemNum]  DEFAULT ('') FOR [ItemNum]
GO

ALTER TABLE [dbo].[testtb2] ADD  CONSTRAINT [DF_testtb2_ItemQty]  DEFAULT ((0)) FOR [ItemQty]
GO

ALTER TABLE [dbo].[testtb2] ADD  CONSTRAINT [DF_testtb2_SoArea]  DEFAULT ('') FOR [SoArea]
GO



 truncate table testtb
 truncate table testtb2

insert into testtb(Sonum,ItemNum,ItemQty,SoArea) values('SO1','Item1001','2000','')
insert into testtb(Sonum,ItemNum,ItemQty,SoArea) values('SO1','Item1002','600','')
insert into testtb(Sonum,ItemNum,ItemQty,SoArea) values('SO2','Item1003','3500','')
insert into testtb(Sonum,ItemNum,ItemQty,SoArea) values('SO2','Item1004','1400','')
insert into testtb(Sonum,ItemNum,ItemQty,SoArea) values('SO2','Item1005','1000','')
insert into testtb(Sonum,ItemNum,ItemQty,SoArea) values('SO3','Item1006','4099','')
select * from testtb
select * from testtb2

 

 

执行语句

 

 insert into  testtb2  select * from testtb  --先备份testtb的数据
 
while exists(select * from testtb where ItemQty>2000)--1\3\4\6
 begin
     declare  @tb01 table(ID int);
     insert into  @tb01(ID) select ID from testtb where ItemQty>2000;
     select * from @tb01  ------1\3\4\6
     insert into testtb    (    Sonum,ItemNum,ItemQty,SoArea)
        select Sonum,ItemNum,2000,SoArea from testtb   where ID in (select ID from @tb01); ---将大于1000的数量再插入到表里面

     update testtb set ItemQty=(ItemQty-2000) where ID in (select ID from @tb01); ---对大于1000的物料数量自动减1000
     delete from  @tb01;
 end
 
 
select * from testtb order by sonum,ItemNum
select * from testtb2