SQL Server Overview

 
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] ')
--*/