insert into A (PriceID,Price,AgenID) select PriceID, Price, 2 from B使用这样的sql语句插入,在select中的第三列指定AgentID的值就可以了
--在表A中插入一新AgentID时,能遍历表B把B表中的数据插入到相应关联的A表字段中--应该是用触发器 create trigger tr_insert on A instead of insert as insert A(PriceID,Price,AgenID) select b.PriceID,b.Price,i.AgentID from B,(select distinct AgenID from inserted) i order by i.AgentID,b.PriceID
create trigger tr_insert on A instead of insert as insert A(PriceID,Price,AgenID) select b.PriceID,case when b.Price>320 then b.Price+1 else b.Price end,i.AgentID from B,(select distinct AgenID from inserted) i order by i.AgentID,b.PriceID
create trigger tr_insert on A instead of insert as insert A(PriceID,Price,AgenID) select b.PriceID,case when b.Price>320 then b.Price+1 else b.Price end,i.AgentID from B,(select distinct AgenID from inserted) i order by i.AgentID,b.PriceID
select PriceID, Price, 2 from B使用这样的sql语句插入,在select中的第三列指定AgentID的值就可以了
create trigger tr_insert on A
instead of insert
as
insert A(PriceID,Price,AgenID)
select b.PriceID,b.Price,i.AgentID
from B,(select distinct AgenID from inserted) i
order by i.AgentID,b.PriceID
有数据如
ID PriceID AgentID Price
1 80 1 300
2 81 1 320
3 82 1 310
表B(PriceID,Price)
有数据如
PriceID Price
80 300
81 400
82 350
现在用什么语句可以得到下表A
ID PriceID AgentID Price
1 80 1 300
2 81 1 320
3 82 1 310
4 80 2 300
5 81 2 401
6 82 2 351
也就是说在表A中插入一新AgentID时,能遍历表B把B表中的数据插入到相应关联的A表字段中
这里有个地方是判断B表中的Price字段值若大于320的话就应将B.Price>320的字段值加1,诸如400+1=401,350+1=351,而300值却没有变
请大侠帮忙!
instead of insert
as
insert A(PriceID,Price,AgenID)
select b.PriceID,case when b.Price>320 then b.Price+1 else b.Price end,i.AgentID
from B,(select distinct AgenID from inserted) i
order by i.AgentID,b.PriceID
instead of insert
as
insert A(PriceID,Price,AgenID)
select b.PriceID,case when b.Price>320 then b.Price+1 else b.Price end,i.AgentID
from B,(select distinct AgenID from inserted) i
order by i.AgentID,b.PriceID