/******* 导出到excel EXEC master..xp_cmdshell ’bcp SettleDB.dbo.shanghu out c:/temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""’
/*********** 导入Excel SELECT * FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, ’Data Source="c:/test.xls";;User ID=Admin;;Password=;;Extended properties=Excel 5.0’)...xactions
/*动态文件名 declare @fn varchar(20),@s varchar(1000) set @fn = ’c:/test.xls’ set @s =’’’Microsoft.Jet.OLEDB.4.0’’, ’’Data Source="’+@fn+’";;User ID=Admin;;Password=;;Extended properties=Excel 5.0’’’ set @s = ’SELECT * FROM OpenDataSource (’+@s+’)...sheet1$’ exec(@s) */
SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+’ ’ 转换后的别名 FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, ’Data Source="c:/test.xls";;User ID=Admin;;Password=;;Extended properties=Excel 5.0’)...xactions
/********************** EXCEL导到远程SQL insert OPENDATASOURCE( ’SQLOLEDB’, ’Data Source=远程ip;;User ID=sa;;Password=密码’ ).库名.dbo.表名 (列名1,列名2) SELECT 列名1,列名2 FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, ’Data Source="c:/test.xls";;User ID=Admin;;Password=;;Extended properties=Excel 5.0’)...xactions