SQL的数据转化脚本.doc

《SQL的数据转化脚本.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、Transact-SQL语句进行导入导出：1.在SQLSERVER里查询access数据:SELECT*FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:DB.mdb";UserID=Admin;Password=')...表名-------------------------------------------------------------------------------2.将access导入SQLserver在SQLSERV

2、ER里运行:SELECT*INTOnewtableFROMOPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','DataSource="c:DB.mdb";UserID=Admin;Password=')...表名-------------------------------------------------------------------------------3.将SQLSERVER表里的数据插入到Access表中在SQLSERVER里运行：insertintoOpe

3、nDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:DB.mdb";UserID=Admin;Password=')...表名(列名1,列名2)select列名1,列名2fromsql表实例：insertintoOPENROWSET('Microsoft.Jet.OLEDB.4.0','C:db.mdb';'admin';'',Test)selectid,namefromTestINSERTINTOOPENROWSET('Microsoft.Jet.O

4、LEDB.4.0','c:trade.mdb';'admin';'',表名)SELECT*FROMsqltablename-------------------------------------------------------------------------------二、SQLSERVER和EXCEL的数据导入导出1、在SQLSERVER里查询Excel数据:SELECT*FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:b

5、ook1.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...[Sheet1$]下面是个查询的示例，它通过用于Jet的OLEDB提供程序查询Excel电子表格。SELECT*FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:Financeaccount.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')..

6、.xactions-------------------------------------------------------------------------------2、将Excel的数据导入SQLserver:SELECT*intonewtableFROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:book1.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...

7、[Sheet1$]实例:SELECT*intonewtableFROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:Financeaccount.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...xactions-------------------------------------------------------------------------------3、

8、将SQLSERVER中查询到的数据导成一个Excel文件T-SQL代码：EXECmaster..xp_cmdshell'bcp库名.dbo.表名outc:Temp.xls-c-q-S"servername"-U"sa"-P""'参数：S是SQL服务器名；U是用户；P是密码说明：还可以导出文本文件等多种格式实例:EXECmaster..xp_cmdshell'bcpsaletesttmp.dbo.C