-- ======================================================
T-SQL代碼：
EXEC master..xp_cmdshell 'bcp 庫(kù)名.dbo.表名out c:/Temp.xls -c -q -S"servername" -U"sa" -P""'
參數(shù)：S 是SQL服務(wù)器名；U是用戶(hù)；P是密碼
EXEC master..xp_cmdshell 'bcp axzq.dbo.Staff out d:staff.xls -c -q -S"." -U"sa" -P"gazx"'
說(shuō)明：還可以導(dǎo)出文本文件等多種格式
實(shí)例: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"'
------------------------------------------------------------------------------------------------
2、在SQL SERVER里往Excel插入數(shù)據(jù):
-- ======================================================
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')