[SQL ] SQL使用bcp汇出csv的问题

楼主: elic2577 (pupx1000你他妈杂碎)   2020-07-08 11:21:47
数据库名称:MS SQL
数据库版本:2014
内容/问题描述:
create table user1
(id int, acct varchar(50), name varchar(50))
insert into user1
values (1, 'A001', '邱小姐')
, (2, 'A002', '吕小姐')
, (3, 'A003', '陈先生')
, (4, 'A004', '蔡先生')
, (5, 'A005', '黄先生')
想汇出每一行的每个字串都被""双引号包住的csv
想要的结果是:
"1","A001","邱小姐"
"2","A002","吕小姐"
"3","A003","陈先生"
"4","A004","蔡先生"
"5","A005","黄先生"
使用bcp的方式
bcp "select * from dbo.user1" queryout "D:\user.csv" -t"\",\"" -r"\"\n\"" -S
192.168.0.1 -d UserDB -U sa -P password -w
但出来的结果是
1","A001","邱小姐"
"2","A002","吕小姐"
"3","A003","陈先生"
"4","A004","蔡先生"
"5","A005","黄先生"
"
差一点点,第一列的"不见了,好像跑去最后一列
请问我该怎么调整最好呢?
非常感谢
作者: cancelpc (阿吉)   2020-07-08 12:53:00
为啥用 /t,用 -q 才对吧
作者: retsamsu   2020-07-08 13:32:00
-t "," -q
作者: cancelpc (阿吉)   2020-07-08 14:45:00
sql 遇到字段用 QUOTENAME(name,'"')-t 只会加在字段后面,当然前面会少了"

Links booklink

Contact Us: admin [ a t ] ucptt.com