导出到TXT文本,用|分开
exec master..xp_cmdshell ''bcp "库名..表名" out "d:\tt.txt" -c -t |-U sa -P password''chi na it p owe r .com25gQajQ4Ziad4TpBULK INSERT 库名..表名
FROM ''c:\test.txt''
WITH (
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''\n''
)chi
exec master..xp_cmdshell ''bcp "库名..表名" out "d:\tt.txt" -c -t |-U sa -P password''chi na it p owe r .com25gQajQ4Ziad4TpBULK INSERT 库名..表名
FROM ''c:\test.txt''
WITH (
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''\n''
)chi
解决方案 »
- sql如何向一个表插入时,向另外一个表插入多行
- 能否将SQL的服务程序和数据文件MDF放在网络中的两台不同的服务器上面?
- 请教一个建表的sql语句。谢谢
- select * from table1 where dt>='2005-08-01' and dt<='2005-08-16' 为什么在执行此语句的时候不包括2005-08-16那天的信息
- SQL2005的查询编辑器中不能使用后退等键盘键
- xml top 问题
- SQL SERVER 高手请进!!!
- 我用递归触发器的问题,加急!!!!!!!!!!!
- 为什么重新安装sql server数据库后在连接以前的数据库不让连接,给出以下提示!
- 一个奇怪的查询结果?!?!?!?!?!
- 复制数据库失败!!!!在线求助!!!!
- SQL语句的长度极限是多少啊?
exec master..xp_cmdshell ''bcp "库名..表名" out "d:\tt.txt" -c -t |-U sa -P password''这个是导入
BULK INSERT 库名..表名
FROM ''c:\tt.txt''
WITH (
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''\n''
)
set @sql='bcp "select ''姓名'',''班级'',''号码'' union all select name as 姓名,bt as 班级,nullif(num,0) as 号码 from 数据库名.dbo.表名" '+
' queryout "输出文件路径" -c -t"|" -r"\r\n" -T'
exec master..xp_cmdshell @sql
"select 你要查的字段名
Union all
Select 你要查的字段名 from 表1 A Inner jion 表2 B on A.关联字段=B.关联字段 where 条件"
out "d:\tt.txt" -c -t |-U sa -P password''
exec master..xp_cmdshell ''
bcp
"select rtrim(b.incn),(case when rtrim(b.addr)<>'0' then rtrim(b.addr) else ''end) ,(case when len(rtrim(pocd))=6 then rtrim(pocd) else ''end),(case when rtrim(b.tlno)<>'0' then rtrim(b.tlno) else ''end),rtrim(c.cpcidty),rtrim(b.idno),rtrim(d.utno_code),rtrim(a.card),'4',rtrim(a.scdt),'','1','3320','0' from tb_down_eviaa a,tb_down_cdsia b,tb_app_idty c,tb_app_utno d where a.scdt='20050809' and a.cuno IS NOT null and ( a.cadt IS null OR a.CADT='0' ) and a.cuno=b.cuno and b.idty=c.idty and a.utno=d.utno and ( ( cpcidty='1' and ( len(rtrim(b.idno))=15 or len(rtrim(b.idno))=18 ) ) or cpcidty!='1')"out "d:\tt.txt" -c -t |-U sa -P sa''请问是什么原因?我这句没有加字段名也不行呀!帮忙!
谢谢!
exec master..xp_cmdshell '
bcp
"select rtrim(b.incn),(case when rtrim(b.addr)<>''0''
then rtrim(b.addr) else ''''end) ,
(case when len(rtrim(pocd))=6 then rtrim(pocd) else ''''end),
(case when rtrim(b.tlno)<>''0'' then rtrim(b.tlno) else ''''end),
rtrim(c.cpcidty),rtrim(b.idno),rtrim(d.utno_code),
rtrim(a.card),''4'',rtrim(a.scdt),'''',''1'',''3320'',''0''
from tb_down_eviaa a,tb_down_cdsia b,tb_app_idty c,tb_app_utno d
where a.scdt=''20050809'' and a.cuno IS NOT null
and ( a.cadt IS null OR a.CADT=''0'' )
and a.cuno=b.cuno and b.idty=c.idty
and a.utno=d.utno
and (( cpcidty=''1'' and ( len(rtrim(b.idno))=15 or len(rtrim(b.idno))=18 ) )
or cpcidty!=''1'')" out "d:\tt.txt" -c -t |-U sa -P password'
exec master..xp_cmdshell '
bcp
"select rtrim(b.incn),(case when rtrim(b.addr)<>''0''
then rtrim(b.addr) else ''''end) ,
(case when len(rtrim(pocd))=6 then rtrim(pocd) else ''''end),
(case when rtrim(b.tlno)<>''0'' then rtrim(b.tlno) else ''''end),
rtrim(c.cpcidty),rtrim(b.idno),rtrim(d.utno_code),
rtrim(a.card),''4'',rtrim(a.scdt),'''',''1'',''3320'',''0''
from tb_down_eviaa a,tb_down_cdsia b,tb_app_idty c,tb_app_utno d
where a.scdt=''20050809'' and a.cuno IS NOT null
and ( a.cadt IS null OR a.CADT=''0'' )
and a.cuno=b.cuno and b.idty=c.idty
and a.utno=d.utno
and (( cpcidty=''1'' and ( len(rtrim(b.idno))=15 or len(rtrim(b.idno))=18 ) )
or cpcidty!=''1'')" out "d:\tt.txt" -c -t |-U"sa" -P"password"'
exec master..xp_cmdshell '
bcp "Select * from Pubs.dbo.jobs" Queryout "d:\jobs.txt" -c -t"|" -S"服务器名" -U"sa" -P"sa"'
该你的代码吧。exec master..xp_cmdshell '
bcp "Select * from Pubs.dbo.jobs" Queryout "d:\jobs.txt" -c -t"|" -S"DYS_1" -U"sa" -P"sa"'