Cross-database server queries and cross-table updates

This article explains how to implement data query across database servers and SQL syntax for cross-table updates through the actual business scenario.

Two. Business Scenarios

The function that I want to implement is very simple, in my local one table is used to save the province information: t_province

On the other server there is also a saved table province, which has provience_name_en and provience_id information that I do not have locally. I want to save them to my table.

Although the program is easy to implement, but I think it would be easier to manipulate the database directly, but also to learn the cross-server query knowledge.

Three. Preparatory work

First I added the Provincenameen and Provinceid two fields to the local T_province table. Next, we'll find a way to populate the data for both fields.

Four. Cross-server queries

The first thing you need to do is troubleshoot cross-server queries. First look at my final implementation:

', @provider =null, @rmtuser =  

The above uses sp_addlinkedserver and sp_addlinkedsrvlogin to establish a link with the server, then you can directly query the remote server data:

--Creating a temporary table CREATE table  #t  (provincename nvarchar, provincenameen nvarchar (50), Provinceid nvarchar) INSERT into #t (Provincename, Provincenameen, Provinceid) (    SELECT localdb.provincename,  serverdb.province_ Name_en,  serverdb.province_id as      LocalDB, as  serverdb    WHERE localdb.provincename =  

With the SQL statement above, I made an inline query of two tables of two servers, two databases, and saved the results to the local temporary table #t.

Five. Cross-table updates

Next, you want to update the data in the #t to the T_province table. In fact, the cross-table update is simple, but in the beginning of the concept of the mind, do not know how to write the SET clause. Here are the final results:

--Update the local t_province table data update T_provinceset T_province.provincenameen =  (SELECT #t. Provincenameen),    t_ Province.provinceid = (SELECT #t. Provinceid) from t_province, #tWHERE t_province.provincename = #t. Provincename

It is important to note that I first created the @t in the form of the Declare table variable, but when I performed the update operation, I was prompted to "declare a scalar variable @t" and replace it with a temporary table #t there is no problem.

Six. Cross-server query related knowledge

The following is an explanation of the knowledge that is used across server queries.

Create a linked server sp_addlinkedserver

Create a linked server. A linked server allows users to distribute heterogeneous queries against OLE DB data sources. After you create a linked server by using sp_addlinkedserver, you can run distributed queries against that server. If the linked server is defined as an instance of SQL Server, you can execute a remote stored procedure.

Grammar

' Product_Name ']      ' provider_name ']      ' data_source '] ' location      '      ' provider_string '] [ , [ @

Parameters

[@server =] ' Server '

The name of the linked server to be created. The data type of the server is sysname, and there is no default value.

[@srvproduct =] ' Product_Name '

The product name of the OLE DB data source to be added as a linked server. The Product_Name data type is nvarchar (128) and the default value is NULL. If you are SQL Server, you do not have to specify provider_name, Data_source, location, provider_string, and catalog.

[@provider =] ' Provider_name '

The unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source. Provider_name must be unique for the specified OLE DB provider installed on the current computer. The provider_name data type is nvarchar (128) and the default value is NULL, but if provider_name is omitted, SQLNCLI is used. SQLNCLI is the SQL native OLE DB provider. The OLE DB provider should be registered in the registry with the specified PROGID.

[@datasrc =] ' Data_source '

The name of the data source interpreted by the OLE DB provider. The Data_source data type is nvarchar (4000). Data_source is passed as the DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider.

[@location =] ' Location '

The location of the database interpreted by the OLE DB provider. The data type of location is nvarchar (4000) and the default value is NULL. Location is passed as the Dbprop_init_location property to initialize the OLE DB provider.

[@provstr =] ' Provider_string '

An OLE DB provider-specific connection string that identifies a unique data source. The provider_string data type is nvarchar (4000) and the default value is NULL. Provstr or passed to IDataInitialize or set to dbprop_init_providerstring property to initialize the OLE DB provider.

After you create a linked server for the SQL Native Client OLE DB provider, you can use the Server keyword as a server=servername\instancename to specify the instance to specify a specific instance of SQL Server. ServerName is the name of the computer that is running SQL Server, and InstanceName is the name of the specific instance of SQL Server that the user will connect to.

[@catalog =] ' Catalog '

The directory used when establishing a connection to the OLE DB provider. The data type of catalog is sysname and the default value is NULL. Catalog is passed as the Dbprop_init_catalog property to initialize the OLE DB provider. When you define a linked server for an instance of SQL Server, the directory points to the default database to which the linked server is mapped.

Login Link Server sp_addlinkedsrvlogin syntax

' Rmtsrvname '      useself ']      ' locallogin ']      ' rmtuser ']         

Parameters

[@rmtsrvname =] ' Rmtsrvname '

Name of the linked server to which the login mapping is applied. The data type of rmtsrvname is sysname and there is no default value.

[@useself =] ' Useself '

Determine the logon name used to connect to the remote server. The useself data type is varchar (8) and the default value is TRUE.

A value of true specifies that the login uses its own credentials to connect rmtsrvname, ignoring the rmtuser and Rmtpassword parameters. FALSE specifies the rmtsrvname of the specified locallogin using the rmtuser and Rmtpassword parameter connections. If Rmtuser and Rmtpassword are also set to NULL, no login or password is used to connect to the linked server.

[@locallogin =] ' LocalLogin '

Login on the local server. The LocalLogin data type is sysname and the default value is NULL. NULL Specifies that this entry applies to all local logins connected to the rmtsrvname. If it is not NULL, then locallogin can be either SQL Server logon or Windows logon. For Windows logins, they must be granted access to SQL Server either directly or through Windows group memberships that have been granted access.

[@rmtuser =] ' Rmtuser '

When Useself is False, represents the user name used to connect to rmtsrvname. The Rmtuser data type is sysname and the default value is NULL.

[@rmtpassword =] ' Rmtpassword '

The password associated with the rmtuser. The Rmtpassword data type is sysname and the default value is NULL.

Using linked Servers

Server name. Database name. dbo. Table name

Remove linked server sp_dropserver syntax

' Server '      droplogins ' | NULL}]

Parameters

[@server =] ' Server '

The server to be removed. The server has a data type of sysname and no default value. The server must exist.

[@droplogins =] ' Droplogins ' | Null

Indicates that if Droplogins is specified, the associated remote server and linked server logins must also be deleted for the server. The @droplogins data type is char (10) and the default value is NULL.

Cross-database server queries and cross-table updates