Data is exported from one server database table to another server database

Data needs to be imported for the project yesterday, from the database in server97 (official database) to the data to server46 (Test Database). It is usually queried and then copied to another table because the data is small, there is nothing wrong with it, but this time, because some of the tables have a large amount of data, more than 20 million records have been copied and pasted directly, which consumes a lot of I/O and takes more than 20 minutes.

Data needs to be imported for the project yesterday, from the database in server97 (official database) to the data to server46 (Test Database). It is usually queried and then copied to another table because the data is small, there is nothing wrong with it, but this time, because some of the tables have a large amount of data, more than 20 million records have been copied and pasted directly, which consumes a lot of I/O and takes more than 20 minutes.

Data needs to be imported for the project yesterday, from the database in server97 (official database) to the data to server46 (Test Database). It is usually queried and then copied to another table because the data is small, there is nothing wrong with it, but this time, because some of the tables have a large amount of data, more than 20 million records, copy and paste them directly, which consumes a lot of I/O and has failed over 20 minutes, it is depressing. I was going to export the data to Excel and import it to the database again, but Excel could not accommodate so much data at a time.

No way, I had to go online to query how to import data across servers, and finally solved the problem. The import was successful after more than three seconds of more than 0.2 million records. Haha! The code is red. The specific method is as follows:

Use openrowset to connect to a remote SQL statement or insert data

-- For temporary access, you can directly use openrowset

-- Query example

Select * from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

-- Import example

Select * into table from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

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

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

-- Delete the linked server when it is no longer in use

Exec sp_dropserver 'srv _ lnk ', 'droplogins'

Go