对于跨不同服务器的sql脚本执行语言的摘要

对于相关的数据库脚本的手动执行的数据互导功能！

       关键的重点在于建立的连接关系，连接建立好后执行的数据库相关的脚本实现就方便的多了~~~~

1。从一个数据库的一张表B中向另外的一个数据库的表A（起码有相同的字段属性，或字段值）

insert into  A(字段1，字段2。。。字段n)

select 字段1，字段2，120，...字段n

from OPENDATASOURCE('SQLOLEDB','Data Source=B所在的服务器;User ID=用户名;Password=登陆口令').B库.dbo.B表

where 字段1=？ and ...

该操作一般用在查询分析器中！

2。游标的利用，在服务器间传递数据的值

declare @GEN_ID nvarchar(4000)

declare @QUAN nvarchar(4000)

declare @FEE_STATUS_OPERATOR nvarchar(4000)

declare @FEE_STATUS_OPERATETIME nvarchar(4000)

declare @status_operateid nvarchar(4000)

declare @client_sname nvarchar(4000)

BEGIN TRAN STATUS

       declare USR cursor for select d.字段1,d.字段2  from  OPENDATASOURCE(

                'SQLOLEDB',

                'Data Source=服务器1;User ID=;Password='

               ).库1.dbo.表1 as g inner join OPENDATASOURCE(

                'SQLOLEDB',

                'Data Source=服务器1;User ID=;Password='

               ).库1.dbo.表1 as d  on g.字段1=d.字段1 inner join OPENDATASOURCE(

                'SQLOLEDB',

                'Data Source=服务器1;User ID=;Password='

               ).库2.dbo.表1‘ as s on g.字段1=s.字段1 where g.字段2=0 and g.字段3=0 and s.字段4=6

              open USR

              fetch next from USR into @GEN_ID,@QUAN

              WHILE @@FETCH_STATUS = 0

                     BEGIN

                     update 库A.dbo.表 set 字段=@QUAN where 字段1=rtrim(@GEN_ID)

                     fetch next from USR into  @GEN_ID,@QUAN

                     end

              close USR

              deallocate USR

 

       declare USR1 cursor for select 字段1，字段2，120，...字段n

              from  OPENDATASOURCE(

                'SQLOLEDB',

                'Data Source=服务器1;User ID=;Password='

               ).库1.dbo.表1 as g inner join OPENDATASOURCE(

                'SQLOLEDB',

                'Data Source=服务器1;User ID=;Password='

               ).库1.dbo.表1 as d  on g.字段1=d.字段1

              where g.字段2=0 and g.字段3=0 and s.字段4=6

              open USR1

              fetch next from USR into @GEN_ID,@FEE_STATUS_OPERATOR,@FEE_STATUS_OPERATETIME,@status_operateid,@client_sname

              WHILE @@FETCH_STATUS = 0

              BEGIN

              insert into  A(字段1，字段2。。。字段n)

                     values(@GEN_ID,'2','0',@FEE_STATUS_OPERATOR,@FEE_STATUS_OPERATETIME,@status_operateid,@client_sname)

             

              fetch next from USR1 into @GEN_ID,@FEE_STATUS_OPERATOR,@FEE_STATUS_OPERATETIME,@status_operateid,@client_sname

              end

       close USR1

       deallocate USR1

 

       ----------------说明对于游标的利用可以多次，唯一的缺点的就是执行的时间过长！对于这方面要权衡考虑！

 

 

if (@@error<>0)

       begin

              rollback tran STATUS

       end

else

       begin

              commit tran STATUS

       end

------------事物处理