Server | scripts | execution
Data-cross-guide for manual execution of related database scripts!
The key point is to establish the connection relationship, the connection established after the implementation of the database-related script implementation is much more convenient ~ ~ ~ ~
1. Table A (at least the same field properties, or field values) from a table B in a database to another database
Insert into A (field 1, Field 2 ...) Field N)
Select field 1, Field 2,120, ... Field N
From OpenDataSource (' SQLOLEDB ', ' Data source=b server; User id= username; password= login password '). B Library. DBO.B table
where field 1 =? And ...
This operation is commonly used in Query Analyzer!
2. The use of cursors, the value of passing data between servers
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. field 1,d. Field 2 from OpenDataSource (
' SQLOLEDB ',
' Data source= server 1; User id=; Password= '
). Library 1.dbo. Table 1 as G inner join OpenDataSource (
' SQLOLEDB ',
' Data source= server 1; User id=; Password= '
). Library 1.dbo. Table 1 as D on G. field 1=d. Field 1 INNER join OpenDataSource (
' SQLOLEDB ',
' Data source= server 1; User id=; Password= '
). Library 2.dbo. Table 1 ' as S on G. field 1=s. Field 1 where G. field 2=0 and G. Fields 3=0 and S. Fields 4=6
Open USR
FETCH NEXT from the USR into @GEN_ID, @QUAN
While @ @FETCH_STATUS = 0
BEGIN
Update Library a.dbo. Table Set field = @QUAN where field 1=rtrim (@GEN_ID)
FETCH NEXT from the USR into @GEN_ID, @QUAN
End
Close USR
Deallocate USR
Declare USR1 cursor for SELECT field 1, Field 2,120, ... Field N
From OpenDataSource (
' SQLOLEDB ',
' Data source= server 1; User id=; Password= '
). Library 1.dbo. Table 1 as G inner join OpenDataSource (
' SQLOLEDB ',
' Data source= server 1; User id=; Password= '
). Library 1.dbo. Table 1 as D on G. field 1=d. Field 1
where G. field 2=0 and G. Fields 3=0 and S. Fields 4=6
Open USR1
FETCH NEXT from USR into @GEN_ID, @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
----------------illustrate the use of cursors can be multiple times, the only drawback is that the execution time is too long! For this aspect to weigh the consideration!
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
