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

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.