How to extract pipe-delimited data using BCP?

I tried the following command

USE 
SELECT 'exec master..xp_cmdshell'
+ ' '''
+ 'bcp'
+ ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ ' out'
+ ' D:\'
+ TABLE_NAME + '.csv'
+ ' -c'
+ ' -t,'
+ ' -T'
+ ' -S' + @@SERVERNAME
+ ''''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

I want to get the data in following format.

Format will be Pipe delimited text file, with double quote text qualifiers and no header row. How to modify or write the BCP command to get the required result.

My dataset is just a customer table.

CustomerID  CustomerName         ContactName   Address        City    PostalCode  Country
1           Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209       Germany

Required data format is

1|"Alfreds Futterkiste"|"Maria Anders"|"Obere Str. 57 Berlin"|12209|"Germany"

Now that I better understand that you are trying to extract data, here is a new answer. This just simply extracts data from a table that I created in tempdb.

use tempdb

-- drop table mytable

create table mytable 
(id int, customer_name varchar(55), cityname varchar(55), statename varchar(55))

insert into mytable 
values (1, 'a', 'a city', 'a state')
     , (2, 'b', 'b city', 'b state')
     , (3, 'c', 'c city', 'c state')
     , (4, 'd', 'd city', 'd state')

select cast(id as varchar(10)), customer_name, cityname, statename from mytable

Which results in the following bcp command

bcp "select cast(id as varchar(10)), customer_name, cityname, statename from mytable" queryout c:\temp\myTable.csv /S.\instance_name /d tempdb /c /t"|" -T

Update This bcp command will add double quotes

exec Master..xp_Cmdshell 'bcp "select cast(id as varchar(10)), quotename(customer_name,char(34)), quotename(cityname,char(34)), quotename(statename,char(34)) from mytable" queryout "c:\temp\myTable.csv" /S.\instance_name /d tempdb /c /t"|" /T'

or just with bcp

bcp "select cast(id as varchar(10)), quotename(customer_name,char(34)), quotename(cityname,char(34)), quotename(statename,char(34)) from mytable" queryout "c:\temp\myTable.csv" /S.\instance_name /d tempdb /c /t"|" /T

This bcp command uses the quotename function. Which allows you to delimit your output using whatever characters you like.

StackExchange Database Administrators Q#111378, answer score: 8