對於跨不同伺服器的sql指令碼執行語言的摘要

來源:互聯網
上載者:User
伺服器|指令碼|執行
對於相關的資料庫指令碼的手動執行的資料互導功能!

       關鍵的重點在於建立的串連關係,串連建立好後執行的資料庫相關的指令碼實現就方便的多了~~~~

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

------------事物處理


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。