以下游标实现的功能,将A数据库的表数据批量添加到B数据库的表中,然后在B数据库中先添加相应的字段,然后根据相应的字段进行联表更新;
一、利用游标批量添加字段
declare @table_name varchar(300)
declare @mSQL1 varchar(2000)
declare @mSQL2 varchar(2000)
declare @mSQL3 varchar(2000)
declare MyCur cursor for
select table_name from ARCHIVETABLE where table_name not like '%_FILE%'
open MyCur;
fetch next from MyCur into @table_name
while @@fetch_status = 0
begin
set @mSQL1= 'alter table '+ @table_name+' add BMMC nvarchar(64)'
set @mSQL2= 'alter table '+ @table_name+' add DALXMC nvarchar(64)'
set @mSQL3= 'alter table '+ @table_name+' add TEMPMC nvarchar(64)'
print(@mSQL1);
print(@mSQL2);
print(@mSQL3);
execute (@mSQL1)
execute (@mSQL2)
execute (@mSQL3)
set @mSQL1='';
set @mSQL2='';
set @mSQL3='';
fetch next from MyCur into @table_name
end
close MyCur
deallocate MyCur
二、利用游标批量联表更新多字段数据
declare @table_name varchar(300)
declare @mSQL1 varchar(4000)
declare MyCurUpdate cursor for
select table_name from ARCHIVETABLE where table_name not like '%_FILE%'
open MyCurUpdate;
fetch next from MyCurUpdate into @table_name
while @@fetch_status = 0
begin
set @mSQL1= 'update '+@table_name+' set BMMC = O.JG_NAME, DALXMC=U.TABLE_DALX , TEMPMC = U.TABLE_CNAME from JG O,A_WS_AJ T,UserTableType U where T.bmid=O.bumenbianhao and T.dalx = U.TABLE_EDALX';
print(@mSQL1);
execute (@mSQL1)
set @mSQL1='';
fetch next from MyCurUpdate into @table_name
end
close MyCurUpdate
deallocate MyCurUpdate
------------将几个功能点合在一起-----------------------------------------------------------------------------------
declare @ARCHIVE_NAME nvarchar(300)
declare @MIDDLE_TABLE_NAME nvarchar(300)
declare @ARCHIVE_TABLE_NAME varchar(300)
declare @ISFILE varchar(32)
declare @addField1 varchar(500)
declare @addField2 varchar(500)
declare @addField3 varchar(500)
declare @InsertMiddlerows varchar(1000)
declare @InsertMiddlefile varchar(1000)
declare @UpdateMiddlerows varchar(1000)
declare MyCurUpdate cursor for
select ARCHIVE_NAME,MIDDLE_TABLE_NAME,ARCHIVE_TABLE_NAME,ISFILE from ARCHIVETABLE
open MyCurUpdate;
fetch next from MyCurUpdate into @ARCHIVE_NAME,@MIDDLE_TABLE_NAME,@ARCHIVE_TABLE_NAME,@ISFILE
while @@fetch_status = 0
begin
if(@ISFILE='N')
begin
set @InsertMiddlerows = 'SELECT * into middleArchive.dbo.'+@MIDDLE_TABLE_NAME+' FROM datest.dbo.'+@ARCHIVE_TABLE_NAME+' where 1<>1';
set @UpdateMiddlerows= 'update '+@MIDDLE_TABLE_NAME+' set BMMC = O.JG_NAME, DALXMC=U.TABLE_DALX , TEMPMC = U.TABLE_CNAME from JG O,A_WS_AJ T,UserTableType U where T.bmid=O.bumenbianhao and T.dalx = U.TABLE_EDALX';
set @addField1= 'alter table '+ @MIDDLE_TABLE_NAME+' add BMMC nvarchar(64)'
set @addField2= 'alter table '+ @MIDDLE_TABLE_NAME+' add DALXMC nvarchar(64)'
set @addField3= 'alter table '+ @MIDDLE_TABLE_NAME+' add TEMPMC nvarchar(64)'
end
if(@ISFILE='Y')
begin
set @InsertMiddlefile = 'select a.ZJ ID,a.SRC_KEY RECID,a.FILENAME,a.YWLJ FILEPATH,a.FILETYPE SUFFIX,b.FILESIZE into middleArchive.dbo.'+@MIDDLE_TABLE_NAME+' from datest.dbo.'+@ARCHIVE_TABLE_NAME+' a,datest.dbo.'+@ARCHIVE_TABLE_NAME+'_EX b where a.SRC_KEY=b.SRC_KEY and 1<>1';
end
print(@InsertMiddlerows);
print(@InsertMiddlefile);
print(@addField1);
print(@addField2);
print(@addField3);
print(@UpdateMiddlerows);
execute (@InsertMiddlerows)
execute (@InsertMiddlefile)
execute (@UpdateMiddlerows)
execute (@addField1)
execute (@addField2)
execute (@addField3)
set @InsertMiddlerows='';
set @InsertMiddlefile='';
set @UpdateMiddlerows='';
set @addField1='';
set @addField2='';
set @addField3='';
fetch next from MyCurUpdate into @ARCHIVE_NAME,@MIDDLE_TABLE_NAME,@ARCHIVE_TABLE_NAME,@ISFILE
end
close MyCurUpdate
deallocate MyCurUpdate
-----------------------------------------------------------------------------------