SqlServer 游标的简单应用

declare my_cursor cursor
for
select [name] from sysobjects where xtype='U' and charindex('KaoQin',[name])>0 and [name]!='KaoQin' order by crdate
declare @tname varchar(20)
open my_cursor
fetch next from my_cursor into @tname
while(@@fetch_status=0)
  begin
    print 'Table Name: ' + @tname
    exec ('Insert into TOther([CompanyID],[Type],[StaffID],[StaffTrueName]) 
select [CompanyID],[Type],[StaffID],[StaffTrueName],[StaffTypeID],[StaffTypeName] from ['+@tname+'] where UpStates!=0')
    fetch next from my_cursor into @tname
  end
close my_cursor
deallocate my_cursor

以上代码实现：遍历多个分表并将分表的数据插入到其他表中。

INSERT INTO SELECT语句

      语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1

      要求目标表Table2必须存在