Import and Export

1. Open another database

1.

Open the table of another SQL Server database

(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 a table in Access2000

(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 no.> 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. Import and Export

1. Use the BCP tool to import and 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 ')

-- Query and export

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 "'

-- Export directly

Exec master .. xp_mongoshell 'bcp new_ks.dbo.class out "E:/tx3.txt"-C-s "sprogram"-U "develop"-P "12345 "'

 

-- Direct import

Exec master .. xp_mongoshell 'bcp "database. DBO. Data Table" in C:/dt.txt-C-S server name-u username-P password'

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'

 

-- Import With BULK INSERT

Bulk insert hdh. DBO. Kind

From 'e:/tx1.txt'

With (

Fieldterminator = '/t ',

Rowterminator = '/N'

)

The prerequisite is that the table such as kind needs to exist, and the number of fields is the same as that of test.txt.

Run in the query analyzer and Log On As A sa user

 

Bulk insert tbyhhk from 'C:/t.txt 'with (formatfile = 'C:/bcp.txt ')

 

 

3. Other Database Import 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] ')
--*/