22,210
社区成员
发帖
与我相关
我的任务
分享
select (right(REPLICATE(' ',10)+ltrim(列名1),10)),(right(REPLICATE(' ',10)+ltrim(列名1),10)) from dbname..tablename
--或者
select cast(列名1 as char(10)), cast(列名1 as char(10)) from dbname..tablename
EXEC master..xp_cmdshell 'bcp "Select 列名1,列名2,... union all select * from dbname..tablename" queryout c:\DT.xls -c -Sservername -Usa -Ppassword'
/******* 导出到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)
*/
导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'
--导出文本文件
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'