Use SQL statements to import table data from a remote SQL Server database to the corresponding table in the local database

How to use SQL statements to import data from each table in a remote SQL Server database to the corresponding table in the local database
Posting time:

How can I use SQL statements to import data from each table in a remote SQL Server database to the corresponding table in the local database?
The table structure is identical. I heard that DTS is more convenient, but it is not used

I. method 1

1. How to register a remote database to a local database:

 

Exec sp_addrole server 'name', '', 'sqloledb', 'remote database address' Exec sp_add1_srvlogin 'name', 'false', 'local user name', 'remote user name', 'remote user password'

 

2. Import the table

 

Insert database. DBO. Table Select * from remote name. DBO. Table

 

After establishing a connection, try again

Method 2

Access the databases on different computers (remote access is the same as connecting to the network). If you frequently access databases or have a large amount of data, we recommend that you use the link server method.

1. Create a linked server

 

Exec sp_addmediaserver 'srv _ lnk ', '', 'sqlodb', 'remote server name or IP address' Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'username', 'Password' Exec sp_serveroption 'srv _ lnk ', 'rpc out', 'true' -- this allows you to call stored procedures on the linked server. Go

2. Example

 

 

-- 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

 

3. If it is no longer in use, delete the linked server

 

Exec sp_dropserver 'srv _ lnk ', 'droplogins'

 

3. Method 3

For temporary access, you can directly use OpenRowSet.

1. Example 1

 

-- Query example Select * From OpenRowSet ('sqloledb' , 'SQL Server name'; 'username'; 'Password', database name. DBO. Table name) -- Import example Select * into table from OpenRowSet ('sqloledb' , 'SQL Server name'; 'username'; 'Password', database name. DBO. Table name)

2. Example 2 (the test is normal in this example)

 

 

Insert database name. DBO. Table Select * from From OpenRowSet ('msdasql ', 'Driver = {SQL Server}; server = remote server address; uid = username; Pwd = password', table) as

Iv. Related Questions

 

Q: Is there a problem when the imported data volume is large and the network is suddenly interrupted?

A: If it is interrupted, nothing is done, that is, transaction rollback and re-running.

V. Subsequent Problems

Q: In a table, we usually define the self-increment columns and primary keys, while the self-increment columns are also related. Because the incremental columns in the inverted table do not change with the original table, the association relationship is lost. The common practice is:

 

Set identity_insert table name on Insert into Table Name (field, field, field) values (64, 'Han ', 'guo ') Set identity_insert table name off

 

That is, use "set identity_insert table name on" to disable auto increment. However, note that,When there is no self-increment field in the table, using this command produces an error.

Therefore, do not use "set identity_insert table name on" for "insert" operations in tables without self-increment fields ".