How to copy table data across databases on SQL Server

There are many methods to copy table data across databases. The most common method is to writeProgramBut this method is not the optimal method. Today we use a very sharp method that can be executed perfectly in SQL Server 2005 and SQL Server 2008!

The format is as follows:

 
Insert into tablea select * From OpenDataSource ('sqlodb', 'Data source = 127.0.0.1; user id = sa; Password = sasasa '). databasename. DBO. tableb
 

After finding this method, we were ready to execute it, but it was not very smooth. Two errors occurred successively while replicating table data across databases. The first error was:

SQL Server blocked access to statement 'openrowset/OpenDataSource 'of component 'ad hoc distributed queries 'because this component is turned off as part of the security configuration for this server. a system administrator can enable the use of 'ad hoc distributed queries 'by using sp_configure. for more information about enabling 'ad hoc distributed queries ', see "surface area configuration" in SQL Server books online.

Translation:

The SQL Server blocks the 'ad hoc distributed queries'
Statement 'openrowset/OpenDataSource'
Because this component has been disabled as part of the server security configuration. The system administrator can use sp_configure to enable 'ad hoc
Distributed queries '. For more information about enabling 'ad hoc distributed querys', see SQL
"Peripheral application configurator" in server books online ".

 

Solution:

 

Enable ad hoc distributed queries:

 
Exec sp_configure 'show advanced options', 1 reconfigureexec sp_configure 'ad hoc distributed queries ', 1 reconfigure
 

Disable ad hoc distributed queries after the insertion is complete:

 
Exec sp_configure 'ad hoc distributed queries ', 0 reconfigureexec sp_configure 'show advanced options', 0 reconfigure
 

Error 2:

An explicit value for the identity column in table 'cms _ tagsubject 'can only be specified when a column list is used and identity_insert is on.

This is really tangled, there is no way, only Google, and later found that you can add before and after the executed SQL statement:

 
Set identity_insert tablea on -- sqlset identity_insert tableb on
 

After trying, I found that this method could not be solved, but I had to find a solution in a foreign forum for a long time, that is, to write detailed information about the queried columns.

 

Solution:

Insert IntoTablea (column1, column2 .....)
Select * From
OpenDataSource('Sqloledb','Data Source = 127.0.0.1, 3422; user id = sa; Password = sasasa;'). Databasename. DBO. tableb
 

Finally, it was done. In addition, this method can still be directly queried from the Excel file. Haha, it is really powerful:

Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'excel 8.0; IMEX = 1; HDR = yes; database = D: \ a.xls', [sheet1 $])

It is a good way to copy table data across databases.