产品表(product)
PRO_ID PRO_NAME PRO_STORAGE
1 Y手机 1
2 IBM电脑 3
----------------------------------
仓库表(storage)
storage_id storage_name
1 通讯仓库
2 电脑仓库
3 水果仓库
....
---------------------------------
当新增产品时(pro_id=3).同时在库存表中(batch)得到
PRO_ID STORAGE_ID
3 1
3 2
3 3
PRO_ID PRO_NAME PRO_STORAGE
1 Y手机 1
2 IBM电脑 3
----------------------------------
仓库表(storage)
storage_id storage_name
1 通讯仓库
2 电脑仓库
3 水果仓库
....
---------------------------------
当新增产品时(pro_id=3).同时在库存表中(batch)得到
PRO_ID STORAGE_ID
3 1
3 2
3 3
insert into batch(pro_id,storage_id)
select 3,storage_id from storage
PRO_ID int, PRO_NAME nvarchar(10), PRO_STORAGE int)
insert into product select 1, 'Y手机' , 1
insert into product select 2, 'IBM电脑', 3
----------------------------------
create table storage(
storage_id int, storage_name nvarchar(20))
insert into storage select 1 , '通讯仓库'
insert into storage select 2, '电脑仓库'
insert into storage select 3, '水果仓库'
go
select max(product.pro_id)+1 as PRO_ID,storage.storage_id from product,storage group by storage.storage_id
go
drop table product,storage
/*
PRO_ID storage_id
----------- -----------
3 1
3 2
3 3(3 行受影响)
*/
begin transaction if(@@error=0)
begin
INSERT INTO [dbo].[product] WITH(TABLOCKX)(
[PRO_ID],
[PRO_NAME],
[PRO_STORAGE],
[ISLock]
)
VALUES (1,'通讯仓库',3
)
end
if(@@error=0)
begin
DECLARE @custID int
set @proID=( select max(PRO_ID) from product WITH(TABLOCKX) )
INSERT INTO [dbo].[storage]([PRO_ID],
[STORAGE_ID] )
VALUES (
@proID,
'3'
)
end
if(@@error<>0)
begin
SET @ReturnValue = 1 -----------数据添加失败
rollback transaction
end
else
begin
SET @ReturnValue = 0 -----------数据添加成功
commit transaction
end
end
我用的是:
insert into batch(pro_id pro_storage)
select max(a.pro_id)+1,b.storage_id from product a,storage b group by b.storage_id
得到的结果是:
3 1
3 2
3 3
4 1
4 2
4 3