MSSQL uses linked servers for rapid data migration

In the work, encountered a task, need to migrate a database data to B database, two database data structure is the same. b database is already in a considerable amount of data, and therefore cannot be achieved by replicating the database.

Old method

When migrating data, the following methods are used at the outset:

(1) Use SSMs's "Generate and publish Scripts" feature to export data from a database to SQL scripts.

The script looks like this:

INSERT [dbo].[ Region]([ID],[region_id],[Region_name],[Region_code])VALUES(10649,0N'other'N'0000000')INSERT [dbo].[ Region]([ID],[region_id],[Region_name],[Region_code])VALUES(1,2N'Chengdu'N'1398098')INSERT [dbo].[ Region]([ID],[region_id],[Region_name],[Region_code])VALUES(2,2N'Chengdu'N'1360804')INSERT [dbo].[ Region]([ID],[region_id],[Region_name],[Region_code])VALUES(3,2N'Chengdu'N'1398198')INSERT [dbo].[ Region]([ID],[region_id],[Region_name],[Region_code])VALUES(4,2N'Chengdu'N'1398195')INSERT [dbo].[ Region]([ID],[region_id],[Region_name],[Region_code])VALUES(5,2N'Chengdu'N'1355114')INSERT [dbo].[ Region]([ID],[region_id],[Region_name],[Region_code])VALUES(6,2N'Chengdu'N'1390807')INSERT [dbo].[ Region]([ID],[region_id],[Region_name],[Region_code])VALUES(7,2N'Chengdu'N'1370804')INSERT [dbo].[ Region]([ID],[region_id],[Region_name],[Region_code])VALUES(8,2N'Chengdu'N'1398071')INSERT [dbo].[ Region]([ID],[region_id],[Region_name],[Region_code])VALUES(9,2N'Chengdu'N'1365808')

(2) Execute the generated SQL script on the B database.

Problems and difficulties

There is no problem in dealing with thousands of data in this way, but when the data reaches 100,000, the speed is really slow.

New method

Through exploration, a new method has been created.

(1) using a linked server, set up a linked server on the database server where the B database resides, pointing to a database. In this way, a database can be directly manipulated in the B database.

(2) Use the INSERT INTO () Select form statement to directly insert the data found in the a database into the B database.

Change the script above to resemble the following:

1 INSERT   into [dbo].[ Region]2([ID] ,3           [region_id] ,4           [Region_name] ,5           [Region_code]6         )7         SELECT  [ID] ,8                 [bundling] ,9                 [region_id] ,Ten                 [Region_name] , One                 [Region_code] A          from    [Xianjie].[A].[dbo].[ Region]

The speed of migration has been increased by more than a hundredfold. Ha ha

MSSQL uses linked servers for rapid data migration