在同一个数据库中有A(company,line,lib...)和B(company,line,lib)两个表, 现在要求先把B中的记录清空,再把A中company值不同的记录(只取company,line,lib)复制到表B中,请问如何用存储过程来实现,请高手指点,本人不胜感激!!
效果如下
A B
company line lib ... company line lib
pp1 a l1 pp1 a l1
pp1 a l1 pp2 b l2
pp2 b l2 pp3 c l4
pp3 c l4注(!!(只与company 有关,line,lib无关))
效果如下
A B
company line lib ... company line lib
pp1 a l1 pp1 a l1
pp1 a l1 pp2 b l2
pp2 b l2 pp3 c l4
pp3 c l4注(!!(只与company 有关,line,lib无关))
if exists(select name from sysobjects where name ='B')
truncate table B
Declare @selstr varchar(8000)
Declare @record1 varchar(30),@record2 varchar(30),@record3 varchar(30)
Declare mycursor cursor for select distinct(company),line,lib from A
Open mycursor
select @selstr=''
--赋值
fetch next from mycursor into @record1,@record2,@record3
while @@fetch_status =0
begin
if @selstr=''
select @selstr='insert into B (company,line,lib) values('''+@record1+''','''+@record2+''','''+@record3+''')'
fetch next from mycursor into @record1,@record2,@record3
-- select @selstr
exec (@selstr)
select @selstr=''
end
--释放
Close mycursor
Deallocate mycursor
GO
as
delete from B
insert into B select company,line,lib from (select distinct * from A) as 表1exec pro1
delete from b
insert into b select company,line,lib from a group by company,line,lib
按你的要求这是最简单的了
as
delete from b
insert into b select company,line,lib from a group by company,line,lib
按你的要求来说这个可能是最简单的了
CREATE PROCEDURE --you Proc
As
BEGIN TRANSACTION
delete from B
insert into B
Select * from A
COMMIT TRANSACTION
end