Get the App
SLTechnology News&Howtos  ›  Database  › 

How to insert data from one server to another by linking a server

Shulou Source: shulou.com Published: 2022-05-31 13:33:07 11月16日 Update

In this issue, the editor will bring you about how to insert the data from one server to another by linking the server. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Server A [10.0.0.1]:

Establish a linked server to Server B

Server B [10.0.0.2]:

Set up a database account for the user to link to the server

Server A:

Execute the script:

Set XACT_ABORT ON

Begin Try

Begin distributed Tran

Insert Into [10.0.0.2] .[B] .[dbo] .[tableB]

(bid

Company

Tel

)

SELECT [bid]

, [company]

, [tel]

FROM [A]. [dbo]. [tableA] t

Where t.codewords 11 'and t.typewriters error'

Commit Tran

End Try

Begin Catch

Rollback Tran

End Catch

Because the id of the target table is self-incrementing, you can not insert the id auto-increment column of the target table, or you can set the id auto-increment column of the target table to insert.

SET IDENTITY_INSERT tableB ON

Let the self-incrementing column of the target table be recorded by insert.

After insert, close the IDENTITY_INSERT of the target table.

SET IDENTITY_INSERT tableB OFF

Here, select the id auto-increment column of the non-insert target table, and let the id self-increment column.

Error report:

The partner transaction manager has banned its support for remote / network transactions

Network DTC access is not enabled on the remote host.

Http://blog.csdn.net/apollokk/article/details/51543349

Both parties start the MSDTC service

(1) on the windows Control Panel-- > Management tools-- > Service-- > Distributed Transaction Coordinator-- > Properties-- > start

(2) it is normal after running "net start msdtc" under CMD to enable the service.

Management tools-component services, check all.

The execution was successful.

This is how the editor shares how to insert the data from one server to another by linking the server. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Tags: Services servers goals links data management transactions content tools management tools networks analysis success expertise small and medium-sized hosts transaction management partners rich content both parties Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno Docker MySQL Microsoft Xiaomi Shulou Information