自己做的例子:
--转移Accode
--把以0开头的表转移到accountcode表
if exists(select * from sysobjects where name='Proc_MoveAccode')
drop proc Proc_MoveAccode
go
create proc Proc_MoveAccode
as
declare @sql nvarchar(1000)
declare @tableName nvarchar(100)
declare cur cursor for
select name from sysobjects where xtype='U' and name like '0%' order by name --搜索所有表名
open cur
while @@fetch_status=0
begin
-- set @sql='select * from [' + @tableName + '] where voucherNo= 100003024 ' --循环查询表数据
set @sql='insert into accountcode (voucherNo,trnDate,particulars,remarks,amount,chequeNo,PayTo,ReceiptNo,VoucherType)
select voucherNo,trnDate,particulars,remarks,amount,chequeNo,PayTo,ReceiptNo,VoucherType from ['+ @tableName +']'
print @sql
exec sp_executesql @sql
fetch next from cur into @tableName
end
close cur
deallocate cur
set nocount off
go