SQL Sever how to copy data across databases "Go"

1, using the OPENDATASOURCE system function

The detailed usage has been commented in the SQL code. This is the inverted data between SQL Server and SQL Server. 2005,2008,2012 should all be applicable.

--Query 100 data from remote server 192.168.66.154 and import into Dbo.dquestiondataInsertIntoDbo.dquestiondataSelectTop100*FromOpenDataSource (‘Sqloledb‘,‘Data Source=192.168.6.154;user id=sa;password=xxxxxx‘). Answer.dbo.DQuestionData--OpenDataSource is a system function, the first parameter is provider Name, the second argument is an OLE DB link string,--Note that there is no database specified in the connection string; The database name, Schema, and table name are written behind the OPENDATASOURCE function.--Executing the above statement will report the error as follows, because the remote query support is not turned on--SQL Server blocked access to STATEMENT ' openrowset/opendatasource ' of component ' Ad Hoc distributed Queries '--Because this component was turned off as part of the security configuration for this server.--A system administrator can enable the use of the "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 Onl Ine.--Workaround 1: In the graphical interface, SQL Server Security Configuration--function Security Configuration--turn on support for remote query OPENROWSET and OPENDATASOURCE--Workaround 2: Use the sp_confing system stored procedure to open it as a command line--You must first turn on the ' Show advanced options ' premium option because ' Ad Hoc distributed Queries ' belongs to advanced optionsexec sp_configure‘Show advanced Options‘ ,1Reconfigure--The following step can be omitted, without parameters of the sp_configure, indicating that all available options are viewed--Remember that you must add reconfigure statements to make the options take effect immediately, or we--I still can't see the ' Ad Hoc distributed Queries ' advanced optionExecsp_configure--Open the ' Ad Hoc distributed Queries ' optionexec sp_configure‘Ad Hoc Distributed Queries ", 1-- Remember to turn these options off after you run out of options exec sp_configure  ad HOC distributed Queries ", 0reconfigureexec sp_configure show advanced Options0reconfigure-- Check to see if it's off. exec sp_configure        

2, using the OPENROWSET system function

--1,microsoft OLE DB Provider for SQL Server--Note the second part of the link string is very strange Server,user,passwrod is connected with ";".SelectTop100*FromOpenRowset‘Sqloledb‘,‘192.168.6.154‘;‘saxxxxx --2,odbc a data source: Microsoft OLE DB Provider for ODBC driversselect top 100 * fromopenrowset (  ' msdasql",  ' 

Both of the above methods use the OLE DB provider name, and the system stored procedure below can see the name of the OLE DB provider

-- for viewing the OLE DB  provider name

3, using a linked server

If you want to use a remote query multiple times, it's a bit cumbersome to write a long string of links each time. Reuse can be considered.

Using a linked server can be a good solution to this problem

--Create a linked serverEXEC sp_addlinkedserver‘Svr_link‘,‘‘,‘Sqloledb‘,‘192.168.6.154‘--Create Login InformationEXEC sp_addlinkedsrvlogin‘Svr_link‘,‘False‘,Null ' sa " ' Xxxx-- query The format is: linked server. The database name. The name of the schema. Table name select top 100 * from Svr_link. Answer.dbo.dquestiondata--exec sp_dropserver  svr_link  ",  droplogins '                 

a few things to note:1, although the above example is from remote server select data to local, but we can also direct local data to the remote server

-- insert two data into Svr_link for the test table of the answer database 192.168.6.154 the remote database  . Answer.dbo.test1,'Zhangall2,'Wang'      

2, with a linked server, you can use the OPENQUERY system function

--Retrieving dataselect * from openquery (Svr_link, select * from answer.dbo.test< Span style= "color: #ff0000;" > ' ) select 3,  ' li     

Both the 3,opendatasource and the linked server are returned only to the server section, and the query table requires further specification of the database, schema, and table name. and OPENROWSET and OPENQUERY are returning the recordset

SQL Sever how to copy data across databases "Go"