Let say I have 1 query for remote SQL Server:
SELECT * FROM USER
And I would like to export using any kind of script without using manual intervention from SSMS. I expect the output to be like this:
id,username,firstName,lastName
1,amy,Amy,NULL
2,bob,Bob,Marley
3,cat,Cathy,NULL
How can I export into something like above?
I noticed there are few old discussions on this with variety of custom workaround that doesn't work for me. I prefer the exported file contains header, NULL value and without start/end quotes. Here's my closest script to achieve the result:
bcp "SELECT * FROM USER;" queryout export.csv -S 'localhost' -U sa -P 'password' -d 'mydatabase' -c -t ','
Current output:
1,amy,Amy,
2,bob,Bob,Marley
3,cat,Cathy,
bcpscript ?