I need to BCP a table into a tab-delimited file, but I need the column names in the first record of the table. Question 1: Am I right that BCP does not have a switch for this? Question 2: If not, why?
I tried to do the following:
BCP "declare @colnames varchar(max); select @colnames=coalesce (@colnames+char(9), '')
+ Column_Name from db.information_Schema.columns where table_name='table1' order by
ordinal_position; select @colnames" queryout Table1_Columns.tsv -S?? -U?? -P?? -f** -e**
The format file looks like this:
9.0
1
1 SQLCHAR 0 100 "\r\n" 1 Column_Names SQL_Latin1_General_CP1_CI_AS
This gets me a file of the column names, then a second BCP command gets me a file of data, and I just DOS-copy the two together. Question 3: Am I clever or what? Question 4: Why doesn't it work? I get the error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Host-file columns may be skipped only when
copying into the Server
SELECT * FROM INFORMATION_SCHEMA. COLUMNS WHERE COLUMN_NAME LIKE 'PREFIX%' ORDER BY TABLE_NAME; Query: SELECT * FROM INFORMATION_SCHEMA.
USE db_name; DESCRIBE table_name; it'll give you column names with the type.
bcp does not support exporting the column headers with the data, however there are some workarounds like exporting the headers in a separate file, then merging both the headers and data files as the following:
exec master..xp_cmdshell 'BCP "select 'SETTINGS_ID','GROUP_NAME'" queryout d:\header.csv -c -T -t,'
exec master..xp_cmdshell 'BCP "select SETTINGS_ID,GROUP_NAME from [DB]..[TABLE]" queryout "d:\columns.csv" -c -t, -T '
exec master..xp_cmdshell 'copy /b "d:\header.csv"+"d:\columns.csv" "d:/result.csv"'
You may also delete the unused files:
exec master..xp_cmdshell 'del "d:\header.csv"'
exec master..xp_cmdshell 'del "d:\columns.csv"'
Or maybe you can combine all the data in a view (adding headers) and export it
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With