文档介绍：踏雪无痕
SQL语句导入导出大全
分类: 数据库 2011-08-19 11:24 275人阅读评论(0) 收藏举报
*******  导出到excel
EXEC master..xp_cmdshell 'bcp out c:/ -c -q -S"GNETDATA/GNETDATA" -
U"sa" -P""'
/***********  导入Excel
SELECT * 
FROM OpenDataSource( '',
  'Data Source="c:/";User ID=Admin;Password=;Extended properties=Excel ')...xactions
/*动态文件名
declare ***@fn varchar(20),***@s varchar(1000)
set ***@fn = 'c:/'
set ***@s =''''',
''Data Source="'+***@fn+'";User ID=Admin;Password=;Extended properties=Excel '''
set ***@s = 'SELECT * FROM OpenDataSource ('+***@s+')...sheet1$'
exec(***@s)
*/
SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+' ' 转换后的别名
FROM OpenDataSource( '',
  'Data Source="c:/";User ID=Admin;Password=;Extended properties=Excel ')...xactions
/********************** EXCEL导到远程SQL
insert OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=远程ip;User ID=sa;Password=密码'
         ).(列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( '',
  'Data Source="c:/";User ID=Admin;Password=;Extended properties=Excel ')...xactions
/** 导入文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:/ -c -Sservername -Usa -Ppassword'
/** 导出文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename out c:/ -c -Sservername -Usa -Pp