1. Open another data volume
1.
Open another data table in SQL Server
(1). Select * From OpenRowSet ('sqloledb', 'sprogram '; 'develop'; '123', 'select * From kind ')
(2). Select * From OpenRowSet ('sqloledb', '192. 1.1.247'; 'develop'; '123', 'select * From kind ')
(3). Select * From OpenDataSource ('sqloledb', 'Data source = sprogram; user id = develop; Password = 100'). new_ks.dbo.kind
2.
Open an Access2000 table
(1 ). select * From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = E:/North. MDB; user id = admin; Password = ')... supply
(2). Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'E:/North. mdb '; 'admin'; '', supply)
(3). Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'E:/North. mdb '; 'admin'; '', 'select * from supply where supplier limit> 10 ')
3.
Open a table in Excel
(1). Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Excel 5.0; HDR = yes; database = E:/test.xls ', sheet1 $)
(2). Select * into table from OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Excel 5.0; HDR = yes; database = E:/test1.xls ', sheet3 $)
Ii. Inbound and Outbound
1. Use the BCP tool to export data
(1) xls file
Exec master .. xp_mongoshell 'bcp "select class_no, kind_no, cn_name from new_ks.dbo.kind order by 1, 2" queryout "E: /test3.xls "-C-Q-s" sprogram "-U" develop "-P" 12345 "'
(2)/** import a text file
-- Import SQL Server
Select * into your table from OpenRowSet ('msdasql ', 'driver = {Microsoft text Driver (*. txt ;*. CSV)}; defaultdir = C:/temp; ', 'select * From contact.txt ')
-- Check the producer token
Exec master .. xp_mongoshell 'bcp "select class_no, kind_no, cn_name from new_ks.dbo.kind order by 1, 2" queryout "E: /tx1.txt "-C-Q-s" sprogram "-U" develop "-P" 12345 "'
-- Directly extract
Exec master .. xp_mongoshell 'bcp new_ks.dbo.class out "E:/tx3.txt"-C-s "sprogram"-U "develop"-P "12345 "'
-- Direct ingress
Exec master.. xp_mongoshell 'bcp "data centers. DBO. Data Tables Table" in C:/dt.txt-C-S server name-u use encrypted-P secret'
Exec master.. xp_mongoshell 'bcp hdh. DBO. Kind in "E:/tx1.txt"-C-Q-S-u-p'
Exec master .. xp_mongoshell 'bcp hdh. DBO. Kind in "E:/test3.xls"-C-Q-S-u-p'
-- Use BULK INSERT
Bulk insert hdh. DBO. Kind
From 'e:/tx1.txt'
With (
Fieldterminator = '/t ',
Rowterminator = '/N'
)
The preceding example shows the existence of a table such as kind, and the number of fields such as test.txt must be as many.
Run in the analyzer, and log on to the analyzer using the SA
Bulk insert tbyhhk from 'C:/t.txt 'with (formatfile = 'C:/bcp.txt ')
3. Other data transfer methods:
(1) --/* dbase iv File
Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Dbase IV; HDR = no; IMEX = 2; database = C:/', 'select * from [customer profile 4.dbf]')
--*/
(2) --/* dbase iii File
Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Dbase III; HDR = no; IMEX = 2; database = C:/', 'select * from [customer profile 3.dbf]')
--*/
(3) --/* FoxPro Database
Select * From OpenRowSet ('msdasql ',
'Driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = C :/',
'Select * from [AA. DBF] ')
--*/