建立表yfccCREATE TABLE [dbo].[yfcc] (
[票号] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[序号] [int] NOT NULL ,
[小号] [int] NOT NULL ,
[产品编号] [char] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[库房] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[批号] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[效期] [datetime] NULL ,
[标识] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[库房数量] [decimal](18, 0) NULL ,
[数量] [decimal](18, 0) NULL ,
[件数] [float] NULL ,
[保管员] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[审核时间] [datetime] NULL ,
[业务1] [bit] NOT NULL ,
[业务2] [bit] NOT NULL ,
[业务3] [bit] NOT NULL ,
[业务4] [bit] NOT NULL ,
[业务5] [bit] NOT NULL ,
[单价] [float] NULL ,
[金额] [decimal](18, 2) NULL ,
[毛利] [decimal](18, 2) NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[yfcc] WITH NOCHECK ADD
CONSTRAINT [PK_yfcc] PRIMARY KEY CLUSTERED
(
[票号],
[序号],
[小号]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
想批量插入数据到yfcc表,自动更新票号累计,由于原数据票号不是顺序排列的例如
票号, 序号, 小号,.....
A23R200802270002 1 1
A23R200802270002 2 1
A23R200802270002 3 1
A23R200802270011 1 1
A23R200802280001 1 1
A23R200802280001 2 1
A23R200802280032 1 1
A23R200802280114 1 1
想通过触发器实现下列效果
票号, 序号, 小号,.....
A23R200802270001 1 1
A23R200802270001 2 1
A23R200802270001 3 1
A23R200802270011 1 1
A23R200802280001 1 1
A23R200802280001 2 1
A23R200802280002 1 1
A23R200802280003 1 1
...
左侧的票号是顺序累计的,请问怎么做,还有其它好办法没?
[票号] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[序号] [int] NOT NULL ,
[小号] [int] NOT NULL ,
[产品编号] [char] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[库房] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[批号] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[效期] [datetime] NULL ,
[标识] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[库房数量] [decimal](18, 0) NULL ,
[数量] [decimal](18, 0) NULL ,
[件数] [float] NULL ,
[保管员] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[审核时间] [datetime] NULL ,
[业务1] [bit] NOT NULL ,
[业务2] [bit] NOT NULL ,
[业务3] [bit] NOT NULL ,
[业务4] [bit] NOT NULL ,
[业务5] [bit] NOT NULL ,
[单价] [float] NULL ,
[金额] [decimal](18, 2) NULL ,
[毛利] [decimal](18, 2) NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[yfcc] WITH NOCHECK ADD
CONSTRAINT [PK_yfcc] PRIMARY KEY CLUSTERED
(
[票号],
[序号],
[小号]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
想批量插入数据到yfcc表,自动更新票号累计,由于原数据票号不是顺序排列的例如
票号, 序号, 小号,.....
A23R200802270002 1 1
A23R200802270002 2 1
A23R200802270002 3 1
A23R200802270011 1 1
A23R200802280001 1 1
A23R200802280001 2 1
A23R200802280032 1 1
A23R200802280114 1 1
想通过触发器实现下列效果
票号, 序号, 小号,.....
A23R200802270001 1 1
A23R200802270001 2 1
A23R200802270001 3 1
A23R200802270011 1 1
A23R200802280001 1 1
A23R200802280001 2 1
A23R200802280002 1 1
A23R200802280003 1 1
...
左侧的票号是顺序累计的,请问怎么做,还有其它好办法没?
票号, 序号, 小号,.....
A23R200802270001 1 1
A23R200802270001 2 1
A23R200802270001 3 1
A23R200802270002 1 1
A23R200802280001 1 1
A23R200802280001 2 1
A23R200802280002 1 1
A23R200802280003 1 1
http://blog.csdn.net/roy_88/archive/2006/12/01/1424370.aspx
instead of insert
as
begin
declare @int int,@date nvarchar(8)
set @date=convert(varchar(8),getdate(),112) select * into #
from inserted
select @int=isnull(max(right([票号],4)),0) from ta
where substring([票号],5,8)=convert(varchar(8),getdate(),112) update # set id='A23R'+@date+right(10000+@i,4),@int=@int+1
insert ta
select * from #
end
[票号] [char] (16) primary key ,
[序号] [int] NOT NULL
)
go
create trigger test_yfcc_tr on yfcc
instead of insert
as
begin
declare @int int,@date nvarchar(8)
set @date=convert(varchar(8),getdate(),112) select * into #
from inserted
select @int=isnull(max(right([票号],4)),0) from yfcc
where substring([票号],5,8)=convert(varchar(8),getdate(),112) update # set 票号='A23R'+@date+right(10000+@int,4),@int=@int+1
insert yfcc
select * from #
endgo
insert yfcc(票号,序号)values(2,1)
insert yfcc(票号,序号)values(2,2)go
select * from yfcc票号 序号
---------------- -----------
A23R200802280001 1
A23R200802280002 2(所影响的行数为 2 行)
没什么用啊!select * from yfcc ordery by 票号 asc,序号 asc,小号 asc
票号, 序号, 小号,.....
A23R200802270002 1 1
A23R200802270002 2 1
A23R200802270002 3 1
A23R200802270011 1 1
A23R200802280001 1 1
A23R200802280001 2 1
A23R200802280032 1 1
A23R200802280114 1 1
想通过触发器实现下列效果
票号, 序号, 小号,.....
A23R200802270001 1 1
A23R200802270001 2 1
A23R200802270001 3 1
A23R200802270002 1 1
A23R200802280001 1 1
A23R200802280001 2 1
A23R200802280002 1 1
A23R200802280003 1 1