SQL Server databases cross-server data import

Today imported from the server to the local surface data, because cross-server, so with a special method

---Create a new table based on the original table, and import the data into
SELECT * Into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)
---already have a table structure
Insert into table select* from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

If the local ad HOC distributed queries service does not open will be an error, prompting you to open

-----Enable AD Hoc distributed Queries:
exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure
After---Use is complete, close the ad Hoc distributed Queries:
exec sp_configure ' Ad Hoc distributed Queries ', 0
Reconfigure
exec sp_configure ' show advanced options ', 0
Reconfigure

For data security, the service is turned off by default. Remember to close when data import is complete

SQL Server databases cross-server data import