Use T-SQL for data export \ import (sqlserver-> sqlserver)

Today, I tried to use Transact-SQL to export and import data. It is much more efficient than DTs! 1. Enable the OpenDataSource function

Start> All Program-> Microsoft SQL Server 2005-> Configuration tool-> SQL Server peripheral application configurator-> function Peripheral application configurator-> Instance name-> Database Engine-> ad hoc remote query-> enable OpenRowSet and OpenDataSource. Otherwise, an error is reported:
Message 15281, level 16, state 1, 1st rows
The SQL Server blocks access to the statement 'openrowset/OpenDataSource 'of the 'ad hoc distributed queries' component because the component is already used as the server The security configuration is disabled. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. Enable 'ad hoc distributed

For more information about queries, see "peripheral application configurator" in SQL Server books online ". 2. Open a remote connection

Start> All Programs> Microsoft SQL Server 2005> Configuration tool> SQL Server peripheral application configurator> service and connected peripheral application configurator> Instance name> Database engine-> remote connection-> Local Connection and remote connection-> only use TCP/IP-> application-> restart Database Engine

Otherwise, an error is reported:
The ole db access interface "sqlncli" of the linked server "(null)" returns the message "Logon timeout expired ".
An error occurred while connecting to the ole db access interface "sqlncli" of the "(null)" server "to return the message" Establishing a connection to the server. Default settings when connecting to SQL Server 2005 The fact that SQL server does not allow remote connection may cause failure. ".
Message 65535, level 16, state 1, 0th rows
SQL Network Interface: An error occurred while obtaining the list of enabled protocols from the registry [xffffffff].

3. Open the client TCP/IP protocol

Start> All Programs> Microsoft SQL Server 2005> Configuration tool> SQL Server Configuration Manager>
SQL Server 2005 network configuration: Enable the TCP/IP protocol
SQL native client configuration: Enabled
Default port-1433

4. Create a connection Server

Exec sp_addmediaserver @ Server = 'condb04 ',
@ Provider = 'sqloledb ',
@ Srvproduct = '',
@ Datasrc = 'winnie \ iidc'
Exec sp_addjavassrvlogin @ rmtsrvname = 'winnie \ iid ',
@ Useself = 'false ',
@ Locallogin = 'admin ',
@ Rmtuser = 'admin ',
@ Rmtpassword = 'Password ';

[Note] Here, the login account should be an account that uses SQL Server Authentication. Otherwise, an error will be reported: XX users cannot log on...

5. Create an empty table in the import target database
Use wwww
Go
Create Table pro_unit_sort (
Culid varchar (40 ),
Coll_unit varchar (400 ),
Cul_sort varchar (400) vi. Execute export \ Import

Select * into wwww. DBO. pro_unit_sort from conndb04.wwww _ xx... DBO. XX. _ unit_sort;

[Note] 1. Steps 4 and 6 are equivalent:
Use wwww
Go
Insert into OpenDataSource (
'Sqlodb ',
'Data source = Winnie \ iidc; user id = admin; Password = password'
). Wwww. DBO. pro_unit_sort
Select * From wwww_xx.dbo.xx.unit_sort; 2. When this operation is performed, UDP port 1434 is used. If the firewall closes this port, open it. Otherwise, an error is reported:

The ole db access interface "sqlncli" of the linked server "(null)" returns the message "Logon timeout expired ".
An error occurred while connecting to the ole db access interface "sqlncli" of the "(null)" server "to return the message" Establishing a connection to the server. Default settings when connecting to SQL Server 2005 The fact that SQL server does not allow remote connection may cause failure. ".
Message 65535, level 16, state 1, 0th rows
SQL Network Interface: An error occurred while obtaining the list of enabled protocols from the registry [xffffffff].

Reference:
1. Use Transact-SQL to import and export data:
Http://tech.it168.com/db/s/2006-08-16/200608160913336_1.shtml 2. SQL server connection Server Technology Summary
Http://www.linuxmine.com/33943.html

This article is from 51cto. com technical blog