SQL2008 inserting database table data from the server into the local database

One, configuration parameters

' Show advanced Options ',1'Ad Hoc distributed Queries',1RECONFIGURE 

If the configuration parameter does not appear, this error is indicated:

SQL Server blocked access to the STATEMENT ' openrowset/opendatasource ' of component ' Ad Hoc distributed Queries ' because this component was shut down as part of this server's security configuration. System administrators can enable ' Ad Hoc distributed Queries ' by using sp_configure. For more information about enabling ' Ad Hoc distributed Queries ', see "surface area Configurator" in SQL Server Books Online.

Second, write the database statement locally

Insert into table name (column name) SELECT * from OpenDataSource ('SQLOLEDB'Data source= 120.0.0.1; User Id=sa; password=123456'). Server database name. Table Name

Note that there is a label in the field of your table, and there are signs that appear:

You cannot insert an explicit value for an identity column in a table when IDENTITY_INSERT is set to OFF

PS: The above column names must match the column names of the database tables

Third, restore the configuration

' Ad Hoc Distributed Queries ',0'Show advanced options',0Reconfigure

SQL2008 inserting database table data from the server into the local database