Import Export Daquan (2004-12-13)
Http://builderman.home.sunbo.net/show_hdr.php?xname=u53dtv0&dname=517dtv0&xpos=1
First, open another database
1.
Open another database of SQL Server2000
(1). Select * from OpenRowSet ('sqloledb', 'sprogram'; 'develop'; '11111', 'SELECT * from Kind')
(2). Select * from OpenRowSet ('sqloledb', '190.1.1.247'; 'develop'; '11111', 'SELECT * from Kind')
(3). Select * from OpenDataSource ('SQLOLEDB', 'Data Source = Sprogram; user ID = wevelop; password = 11111'). New_ks.dbo.kind
2.
Open a table of 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 Number> 10')
3.
Open an Excel's table
(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', SHEET33
Second, import and export
1. Import the export with BCP tools
(1) XLS file
Exec master..xp_cmdshell '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 text file
- into SQLServer
SELECT * INTO Your table from OpenRowSet ('msdasql', 'driver = {Microsoft Text Driver (* .txt; * .csv)}; defaultdir = c: / temp;', 'select * from contact.txt' - - Query export
Exec master..xp_cmdshell '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 to TXT files
Exec master..xp_cmdshell 'bcp db_mrp.dbo.dept out "e: /tx3.txt" -c -s "guide-1" -u "develop" -p "12345"'
- Direct export to XLS files EXEC MASTER..XP_CMDSHEC 'BCP DB_MDSHELL' BCP DB_MRP.DBO.DEPT OUT "E: /TX3.XLS" -c -s "guide-1" -u "develop" -p "12345"
Direct import
Exec master..xp_cmdshell 'bcp "database.dbo. Data table" IN C: /DT.TXT -C -S server name -u user -P password "
Exec master..xp_cmdshell 'bcp hdh.dbo.kind in "E: /TX1.txt" -c -q -s -u -p'
Exec master..xp_cmdshell 'bcp hdh.dbo.kind in "E: /TEST3.XLS" -c -q -s -u -p'
- Import Bulk INSERT
Bulk INSERT HDH.DBO.KIND
From 'e: /tx1.txt'
With
FIELDTERMINATOR = '/ t',
Rowterminator = '/ n'
)
The prerequisites are that, such as Kind wants to exist, and the fields are as many as the number of Test.txt fields.
Run in the query analyzer, and log in with SA users
Bulk INSERT TBYHHK FROM 'C: /T.TXT' with (FormatFile = 'c: /bcp.txt')
3. Import methods of other databases:
