a表
id
1
2
3我想根据a表id批量插入b表和c表id1和sl，其中b表和c表id和a表id关联，示例id=1如下，我想得到当a表id=2 或其它值时，批量插入b表和c表
b表
id id1 sl
1 1 2
1 2 2
1 3 2
1 4 2
1 5 2
1 6 2
c表
id id sl
1 1 1
1 1 2
1 2 1
1 2 2
1 3 1
1 3 2
1 4 1
1 4 2
1 5 1
1 5 2
1 6 1
1 6 2
id
1
2
3我想根据a表id批量插入b表和c表id1和sl，其中b表和c表id和a表id关联，示例id=1如下，我想得到当a表id=2 或其它值时，批量插入b表和c表
b表
id id1 sl
1 1 2
1 2 2
1 3 2
1 4 2
1 5 2
1 6 2
c表
id id sl
1 1 1
1 1 2
1 2 1
1 2 2
1 3 1
1 3 2
1 4 1
1 4 2
1 5 1
1 5 2
1 6 1
1 6 2
-- Author : HappyFlyStone
-- Date : 2009-08-12 23:01:42
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([id] INT)
Go
INSERT INTO ta
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
GO
--Start
SELECT
* ,2 as sl
FROM
TA a
,(select number as N from master..spt_values where type = 'p' and number >0) b
where b.n <= 6
order by id
--Result:
/*id N sl
----------- ----------- -----------
1 1 2
1 2 2
1 3 2
1 4 2
1 5 2
1 6 2
2 1 2
2 2 2
2 3 2
2 4 2
2 5 2
2 6 2
3 1 2
3 2 2
3 3 2
3 4 2
3 5 2
3 6 2(18 行受影响)*/
--End
b表
id id1 sl
1 1 2
1 2 2
1 3 2
1 4 2
1 5 2
1 6 2
c表
id id1 sl
1 1 1
1 1 2
1 2 1
1 2 2
1 3 1
1 3 2
1 4 1
1 4 2
1 5 1
1 5 2
1 6 1
1 6 2对不起c表为id1
讲起来有点复杂为一包装箱
a为包装主表
1箱=6筒=12卷
a表为箱表，b表为筒表，c表为卷表目的是卷满2卷变成一筒，6筒变成一箱，我是想为了计算重量[/Quote]
DROP TABLE [tb]
Go
CREATE TABLE tb([id] INT,id1 int,sl int)
Go
IF OBJECT_ID('[tc]') IS NOT NULL
DROP TABLE [tc]
Go
CREATE TABLE tc([id] INT,id1 int,sl int)
Godeclare @id int,@n int ,@m int
set @id=5
set @n=1
set @m=1while @m<=6
begin
while @n<=@id
begin
insert tb values( @id,@m,2)
set @n=@n+1
end
set @m=@m+1
set @n=1
endselect * from tb
/*
id id1 sl
----------- ----------- -----------
5 1 2
5 1 2
5 1 2
5 1 2
5 1 2
5 2 2
5 2 2
5 2 2
5 2 2
5 2 2
5 3 2
5 3 2
5 3 2
5 3 2
5 3 2
5 4 2
5 4 2
5 4 2
5 4 2
5 4 2
5 5 2
5 5 2
5 5 2
5 5 2
5 5 2
5 6 2
5 6 2
5 6 2
5 6 2
5 6 2
*/
--(30 行受影响)set @n=1
set @m=1
declare @mm int
set @mm=1while @m<=6
begin
while @n<=@id
begin
while @mm<=2
begin
insert tc values(@id,@m,@mm)
set @mm=@mm+1
end
set @mm=1
set @n=@n+1
end
set @n=1
set @mm=1
set @m=@m+1
endselect * from tcid id1 sl
----------- ----------- -----------
5 1 1
5 1 2
5 1 1
5 1 2
5 1 1
5 1 2
5 1 1
5 1 2
5 1 1
5 1 2
5 2 1
5 2 2
5 2 1
5 2 2.......(60 行受影响)
@BZID INT, @JS varchar(10), @ZL decimal(18,3),@xjsl int,@ggid int,@jsl int,@dsl int,@ggbm varchar(10),@gg varchar(10),@xrl varchar(10),@rq datetime,@BZJH varchar(10),@scph varchar(10),@fzs varchar(10)as
BEGIN TRAN
if @ggid=1 or @jsl=@dsl
begin
insert into TSC_BZX(bzxid,zXbm,rq,ggbm,trl,xrl,xnjs,xnts,zzl,zt,bzjh,scph,fzs) values (@ggid,@ggbm,@rq,@gg,@js,@xrl,1,1,@zl,0,@bzjh,@scph,@fzs)
insert into TSC_BZT(bzxid,bztid,sl,zzl) values (@ggid,1,1,@zl)
insert into TSC_BZJ(bzxid,bztid,sl,zl) values (@ggid,1,1,@zl)
end;
else
begin
insert TSC_BZJ select @BZID,case when sl=@JS then bztid+1 else bztid end,case when sl=@JS then 1 else sl+1 end, @ZL from (select top 1 bzxid,bztid,sl from TSC_BZj order by id desc) t
if @xjsl=@js
insert TSC_BZT select @BZID, bztid+1,case when sl=@JS then 1 else sl+1 end,@zl from (select top 1 bzxid,bztid,sl from TSC_BZT order by bzxid desc,bztid desc) t else
update tsc_bzt set tsc_bzt.sl=tsc_bzt.sl+1,zzl=(select sum(zl) as zzl from tsc_bzj where bztid=t.bztid and bzxid=@bzid) from (select top 1 bzxid,bztid,sl from TSC_BZt order by id desc) t where tsc_bzt.bztid=t.bztid and tsc_bzt.bzxid=@BZIDupdate tsc_bzx set zzl=(select sum(zl) as zzl from tsc_bzj where bzxid=@BZID),xnjs=(select count(bzxid) from tsc_bzj where bzxid=@BZID),xnts=(select count(bzxid) from tsc_bzt where bzxid=@BZID) where bzxid=@BZIDGO
这是我完整的包装程序,楼主辛苦了，可是和我的想法不对呀tsc_bzx为a表tsc_bzt为b表,tsc_bzj为c表