用SQL命令导出数据并转换成txt或xls文件

来源：互联网发布：淘宝企业店铺好不好编辑：程序博客网时间：2024/05/29 05:56

SQL命令导出数据

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1.exec master..xp_cmdshell 'bcp ufdata_101_2013.dbo.gl_bautotran out c:\123456789.xls -c -q -S"PC2012102213TYU" -u"sa" -p"123456"'

ufdata_101_2013.dbo.gl_bautotran 为数据库ufdata_101_2013中的gl_bautotran 表 out 盘符:\文件名.格式后缀 -c -q -S"计算机名" -u"用户" -p "密码"

注：-S 大写

2.exec master..xp_cmdshell'bcp ufdata_101_2013.dbo.gl_bautotran out c:\1234567.xls -c -q -t'

这种只要选择数据源和路径不需要密码

SQL命令导入数据

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

EXEC master..xp_cmdshell 'bcp ufdata_001_2013.dbo.gl_bautotran in c:\123456789.xls -c -q -S"PC2012102213TYU" -U"sa" -P"123456"'

列出UFDATA_001_2013数据库中属于用户创建的表

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Select table_name from UFDATA_001_2013.INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'

列出所有数据库名

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select name from master.dbo.sysdatabases

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---备份数据库

backup database "mydb" to disk='c:\mydb.bak' with init

--还原数据库

use master

restore database "mydb" from disk='C:\mydb.bak'

use mydb