T-SQL-access the remote database and manipulate its data tables

Overview

Many times we need to be in. NET application to use a SQL statement to access different tables from different servers and different databases, there are many ways to implement them. We can be in. NET application, use the ADO framework to abstract our databases and data tables into application objects, and use code in the program to connect or otherwise manipulate tables in different libraries , or you can use T-SQL scripts at the database level to query or other operations directly on tables in different databases, the previous way is not too much to introduce, today to introduce the latter.

Let me introduce the environment, I have 2 database servers server_a and Server_b

Server_a (local)

• Database: ADVENTUREWORKLT
• Data sheet: Saleslt.customer

Server_b (remote)

• Database: TailspinToys
• Data table: dbo. Products

Using T-SQL implementation

First we need to add the connection string of the remote database to the local database using the system stored procedure in the local database

[SQL]View Plaincopyprint?

1. exec sp_addlinkedserver ' server_b ', ', 'SQLOLEDB ',' 192.168.0.2 '
2. exec sp_addlinkedsrvlogin ' server_b ',' false ',null,' DbUser ',' Dbpassword '

This command executes only once, and now we have added the remote server_b server to the local, we can use the following command to query the Remote data table or other operations

[SQL]View Plaincopyprint?

1. SELECT B.customerid,a.productid,b.lastname as customername,a.[description] as productname,a.unit,a. Retailprice
2. From Server_B.TailspinToys.dbo.Products as a
3. JOIN AdventureWorksLT.SalesLT.Customer as b
4. On A.productid=b.customerid
5. WHERE b.customerid>4

T-SQL-access the remote database and manipulate its data tables