SQL Server database inserts data across server queries

Distributed query for SQL Server: If a project requires two or more servers, and we have to remove the data from several servers, we must use distributed queries!
Here are two concepts: Local data source, remote data Source!
A local data source refers to data on a single server, while a remote data source refers to data on another SQL instance.
How do I take a number across servers?
One
If you have frequent access or large amounts of data, we recommend that you create a linked server with a linked server
exec sp_addlinkedserver ' srv_lnk ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '
exec sp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' username ', ' password ' go

--query Example:
SELECT * from Srv_lnk. Database name. dbo. Table name

--Import Example:
SELECT * into table from Srv_lnk. Database name. dbo. Table name go--remove linked server when no longer in use

exec sp_dropserver ' srv_lnk ', ' droplogins '
Two
--if it's just temporary access, you can use OPENROWSET directly

--query Example:
SELECT * FROM OPENROWSET (' SQLOLEDB ', ' SQL Server name '; User name '; ' Password ", database name. dbo. Table name)
--Import Example:
SELECT * Into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; User name '; ' Password ", database name. dbo. Table name)

How do I set it up in Enterprise Manager?
SQL Instance---> Security---> Linked server---> Right-click New Linked server General Enter the name of the linked server, select other data sources, select SQLOLEDB, Data source write, general IP address re-select Security-----"SELECT Local login----" The analog tick is made in this security context, and the user and password are entered.

FAQ: 1. After we have established the linked server, we do a query operation example: We have already made a link to the DFLD server, and then we want to access the authors table select * from DFLD in the pubs database. PUBS.. Authors often have two types of problems
1. Tip The heterogeneous query requires the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options, and then re-issue your query. This hint is because the ANSI_NULLS is not opened with Ansi_warnings set ANSI_NULLS on set ANSI_WARNINGS on Select * from DFLD. PUBS.. authors--, that's it!
2. Tip A machine MSDTC service is not available This tip is simple, just open the MSDTC service! Note: The MSDTC service needs to be turned on both sides, and is not turned on by default

SQL Server database inserts data across server queries