SQL Server使用bcp匯入匯出

Transact-SQL語句進行匯入匯出：

1.在SQL SERVER裡查詢access資料:-- ======================================================SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名select *  from opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source="D:\db.mdb";User ID=Admin;Password=')...Student where name = '我'select * from newTable-------------------------------------------------------------------------------------------------2.將access匯入SQL server -- ======================================================在SQL SERVER 裡運行:SELECT *INTO newtableFROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',       'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名-------------------------------------------------------------------------------------------------3.將SQL SERVER表裡的資料插入到Access表中-- ======================================================在SQL SERVER 裡運行：insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名 (列名1,列名2)select 列名1,列名2  from  sql表執行個體：insert into  OPENROWSET('Microsoft.Jet.OLEDB.4.0',    'C:\db.mdb';'admin';'', Test) select id,name from TestINSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名)SELECT *FROM sqltablenameinsert into  OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="D:\db.mdb";User ID=Admin;Password=')...Student(studentid,name) select staffid,name from staffinsert into  OPENROWSET('Microsoft.Jet.OLEDB.4.0',    'D:\db.mdb';'admin';'', Student) select accountid,name from tblbaccountselect * from openrowset('Microsoft.Jet.OLEDB.4.0','D:\db.mdb';'admin';'',Student)-------------------------------------------------------------------------------------------------二、SQL SERVER 和EXCEL的資料匯入匯出1、在SQL SERVER裡查詢Excel資料:SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]下面是個查詢的樣本，它通過用於 Jet 的 OLE DB 提供者查詢 Excel 試算表。SELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactionsSELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',  'Data Source="D:\財務統計科目.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...vib-------------------------------------------------------------------------------------------------2、將Excel的資料匯入SQL server :-- ======================================================SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]執行個體:SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions-------------------------------------------------------------------------------------------------3、將SQL SERVER中查詢到的資料導成一個Excel檔案T-SQL代碼：EXEC master..xp_cmdshell 'bcp 庫名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'參數：S 是SQL伺服器名；U是使用者；P是密碼EXEC master..xp_cmdshell 'bcp axzq.dbo.Staff out d:staff.xls -c -q -S"." -U"sa" -P"gazx"'說明：還可以匯出文字檔等多種格式執行個體:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'EXEC master..xp_cmdshell 'bcp "select name from axzq..staff order by name"  queryout d:staffName.xls -c -q -S"." -U"sa" -P"gazx"'在VB6中應用ADO匯出EXCEL檔案代碼： Dim cn  As New ADODB.Connectioncn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 庫名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"------------------------------------------------------------------------------------------------4、在SQL SERVER裡往Excel插入資料:-- ======================================================insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)T-SQL代碼：INSERT INTO  OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  (bestand, produkt) VALUES (20, 'Test')  -------------------------------------------------------------------------------------------------總結：利用以上語句，我們可以方便地將SQL SERVER、ACCESS和EXCEL試算表軟體中的資料進行轉換，為我們提供了極大方便！EXEC   master..xp_cmdshell   'bcp   "select OrderID,ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Northwind.dbo.orders"   queryout   "d:\Oreders.txt" -t"|"    -c   -q   -S"127.0.0.1"   -U"sa"   -P""'  SELECT  *      FROM  OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data  Source="xxxx.xls";Extended  Properties="Excel  8.0";IMEX=1;Persist  Security  Info=False')...[a1$] 二、SQL SERVER 和EXCEL的資料匯入匯出1、在SQL SERVER裡查詢Excel資料:-- ======================================================SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]下面是個查詢的樣本，它通過用於 Jet 的 OLE DB 提供者查詢 Excel 試算表。SELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactionsSELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',  'Data Source="d:\staff.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...staff-------------------------------------------------------------------------------------------------2、將Excel的資料匯入SQL server :-- ======================================================SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.12.0',  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]執行個體:SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.12.0',  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions-------------------------------------------------------------------------------------------------3、將SQL SERVER中查詢到的資料導成一個Excel檔案-- ======================================================T-SQL代碼：EXEC master..xp_cmdshell 'bcp 庫名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'參數：S 是SQL伺服器名；U是使用者；P是密碼說明：還可以匯出文字檔等多種格式執行個體:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'在VB6中應用ADO匯出EXCEL檔案代碼： Dim cn  As New ADODB.Connectioncn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 庫名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"------------------------------------------------------------------------------------------------4、在SQL SERVER裡往Excel插入資料:-- ======================================================insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)T-SQL代碼：INSERT INTO  OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  (bestand, produkt) VALUES (20, 'Test')  --開啟xp_cmdshell--SQL Server blocked access to procedure 'xp_cmdshell'sp_configure 'show advanced options', 1goreconfiguregosp_configure 'xp_cmdshell', 1goreconfigurego--開啟sp_OACreate--SQL Server blocked access to procedure 'sys.sp_OACreate'sp_configure 'show advanced options', 1;goreconfigure;gosp_configure 'ole automation procedures', 1;goreconfigure;gosp_configure 'Ad Hoc Distributed Queries',1;goreconfigurego