SQL SERVER 和ECEL的数据导入导出.docxVIP

SQL SERVER 和 EXCEL 的数据导入导出 1、在SQL SERVER 里查询Excel 数据: SELECT * FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:\department.xls;User ID=Admin;PassWord=;ExtendedPRoperties=Excel 5.0)...Sheet1$ SELECT * into newtable FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:\department.xls;User ID=Admin;Password=;Extended properties=Excel 5.0)...[Sheet1$] SELECT * FROM dbo.newtable SELECT * FROM dbo.department SELECT w,w2 FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:\book1.xls;User ID=Admin;Password=;Extended properties=Excel 5.0)...Sheet1$ SELECT w4,w3 into newtable2 FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:\book1.xls;User ID=Admin;Password=;Extended properties=Excel 5.0)...[Sheet1$] SELECT * FROM dbo.newtable2 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）...xactions 2、将Excel 的数据导入SQL server : SELECT * into newtable FROM OpenDataSource（ Microsoft.Jet.OLEDB.4.0,Data Source=c:\book1.xls;User ID=Admin;Password=;Extended properties=Excel 5.0）...[Sheet1$] 实例: SELECT * into newtable FROM 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 文件 EXEC master..xp_cmdshell bcptestexcel.dbo.newtable out c:\book8.xls -c -q -S pmserver -Usa -Psa /* 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 au_fname, au_lname FROM pubs..authors ORDER BY au_lname queryout C:\ book9.xls -c -Sservername -Usa -Psa T-SQL 代码： EXEC master..xp_cmdshell bcp 库名.dbo.表名out c:\Temp.xls -c -q -Sservername -Usa -P 参数：S 是