0

I want to rename the column name in the select clause of a bcp queryout command.I've tried the below variations and none of them work.I want to rename the first and third column to email_address and id respectively.

I am calling the bcp command in a batch script.

bcp "select email as 'email_address', first_name, p_id  as 'id' from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,
bcp "select email as [email_address], first_name, p_id  as [id] from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,
bcp "select email 'email_address', first_name, p_id 'id' from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,
bcp "select email email_address, first_name, p_id id from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,

Can someone point me to towards the right solution?

nobody
  • 10,892
  • 8
  • 45
  • 63
  • 1
    Why do you want a column aliased (renamed)? Perhaps you want a column name appear as a header in the file? See this: https://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an – Alex Jul 24 '17 at 18:33
  • You need to tells what you have tried and what and why you did not like it. What you have currently provided as an example does not make sense, making us guess. – Alex Jul 24 '17 at 18:40
  • Column names in the `SELECT` query have no effect on the file produced.Since it is the data that is being stored in the file and not the query itself. – Alex Jul 24 '17 at 18:43
  • As @Alex has already said unless you want to get the column names as part of output, renaming or aliasing would not help. I was just curious to know if the commands you have posted are working queries. At the table_name you would need to fully qualify it i.e. ServerName.SchemaName.TableName. – VKarthik Jul 25 '17 at 03:20
  • Have you tried my proposed solution? – Jasper Schellingerhout Jul 28 '17 at 13:43

1 Answers1

1

If you looked at the text files produced by bcp you'll notice column names are not exported. Only data is exported.

The link Alex posted describes a way to add the column names to the output.You are actually adding the fields as first data column to your data using UNION. I recommend against it, because it requires casting all fields as strings making for a complex query.

Step 1 Output the columns

I would output a text file with the desired column names to one text file. Lets call that file "columnnames.csv". You may even create a repository of fixed column name files if need be.

Step 2 Output the data

Use bcp to output the data as you did before. Lets call that output "data.csv"

Step 3 Combine the two files

You can use this simple batch command to combine the data

copy /b columnnames.csv+data.csv combined.csv

or

type columnnames.csv data.csv > combined.csv

Helpful resources

How to query against the schema to get the column names.

Jasper Schellingerhout
  • 1,070
  • 1
  • 6
  • 24