现在有两个表,表A和表B,其中结构如下:
A
id name type number
-------------------------------
01 aaa 1111 10
02 bbb 2222 8
03 ccc 3333 9B(taishu 为新增字段)
id name type taishu
-------------------------------
01 aaa 1111 null
02 bbb 2222 null
03 ccc 3333 null第一个存储过程为,将更新表B中的taishu字段的值,其值为表A中对应的number值,即b.name=a.name and b.type=a.type!
第二个存储过程为根据一定条件从表B中查询出数据保存到结构相同的表C中!
就是这两个存储过程!
A
id name type number
-------------------------------
01 aaa 1111 10
02 bbb 2222 8
03 ccc 3333 9B(taishu 为新增字段)
id name type taishu
-------------------------------
01 aaa 1111 null
02 bbb 2222 null
03 ccc 3333 null第一个存储过程为,将更新表B中的taishu字段的值,其值为表A中对应的number值,即b.name=a.name and b.type=a.type!
第二个存储过程为根据一定条件从表B中查询出数据保存到结构相同的表C中!
就是这两个存储过程!
update B set taishu=A.number from A where B.name=A.name and B.type=A.type
2、
insert into C select * from B where ...
as
begin
update B set taishu=A.number from A where B.name=A.name and B.type=A.type
endcreate proc 名字
as
begin
insert into C select * from B
end
as
begin
update B set taishu=(select A.number from A where B.name=A.name and B.type=A.type)
endcreate proc 名字
as
begin
insert into C select * from B
end
insert A
select '01','aaa','1111',10 union
select '02','bbb','2222',8 union
select '03','ccc','3333',9create table B(id varchar(10),name varchar(10),type varchar(10),taishu int)
insert B
select '01','aaa','1111',NULL union
select '02','bbb','2222',NULL union
select '03','ccc','3333',NULLcreate table C(id varchar(10),name varchar(10),type varchar(10),taishu int)create procedure p1
as
begin
update B set taishu=a.number
from A
where b.name=a.name and b.type=a.type
endcreate procedure p2(@id varchar(10))
as
begin
insert C
select * from B where id=@id
end
update b set taishu =a.number from a where b.name=a.name and b.type=a.type
create proc procName
as
update B
set taishu=A.number
from
A where B.name=A.name and B.type=A.type2、
create proc procName
as
insert into C
select *
from B
where 条件
仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'plant_plan_huizong' 中为标识列指定显式值。
AS
begin
insert into plant_plan_huizong
SELECT MIN(plant_id) AS plant_id, date_year, date_month, MIN(bargain_name)
AS bargain_name, MIN(bargain_num) AS bargain_num, MIN(product_name)
AS product_name, MIN(parent_name) AS parent_name, MIN(part_id) AS part_id,
part_name, pic_no, stuff, MIN(unit) AS unit, SUM(CAST(all_num AS float)) AS all_num,
SUM(CAST(plan_num AS float)) AS plan_num, MIN(invalidation_date)
AS invalidation_date, MIN(do_time) AS do_time, MIN(maker) AS maker, MIN(auditer)
AS auditer, audit_flag, MIN(re) AS re, plant_flag
FROM plant_plan
GROUP BY date_year, date_month, part_name, pic_no, stuff, plant_flag, audit_flag
ORDER BY plant_id
end
散分!