資源描述：
《SQL的數(shù)據(jù)轉(zhuǎn)化腳本.doc》由會員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。
1、Transact-SQL語句進(jìn)行導(dǎo)入導(dǎo)出：1.在SQLSERVER里查詢access數(shù)據(jù):SELECT*FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:DB.mdb";UserID=Admin;Password=')...表名-------------------------------------------------------------------------------2.將access導(dǎo)入SQLserver在SQLSERV
2、ER里運(yùn)行:SELECT*INTOnewtableFROMOPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','DataSource="c:DB.mdb";UserID=Admin;Password=')...表名-------------------------------------------------------------------------------3.將SQLSERVER表里的數(shù)據(jù)插入到Access表中在SQLSERVER里運(yùn)行：insertintoOpe
3、nDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:DB.mdb";UserID=Admin;Password=')...表名(列名1,列名2)select列名1,列名2fromsql表實(shí)例：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的數(shù)據(jù)導(dǎo)入導(dǎo)出1、在SQLSERVER里查詢Excel數(shù)據(jù):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的數(shù)據(jù)導(dǎo)入SQLserver:SELECT*intonewtableFROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:book1.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...
7、[Sheet1$]實(shí)例:SELECT*intonewtableFROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:Financeaccount.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...xactions-------------------------------------------------------------------------------3、
8、將SQLSERVER中查詢到的數(shù)據(jù)導(dǎo)成一個Excel文件T-SQL代碼：EXECmaster..xp_cmdshell'bcp庫名.dbo.表名outc:Temp.xls-c-q-S"servername"-U"sa"-P""'參數(shù)：S是SQL服務(wù)器名；U是用戶；P是密碼說明：還可以導(dǎo)出文本文件等多種格式實(shí)例:EXECmaster..xp_cmdshell'bcpsaletesttmp.dbo.C
