Uing BCP with multiple result sets

• eletuw

  SSCrazy

  Points: 2464

  More actions

  December 15, 2003 at 5:52 am

  #81702

  How can I use bcp to output multiple result sets to a csv file. That is running several select * from different tables and outputting the results to a single csv file.

  Is there any other way of doing this?

  Thanks.

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  December 15, 2003 at 9:13 am

  #486186

  bcp each select into different file and then concatenate files.

  or

  If all the selects have same columns then use union.

  or

  Concatenate each column (converting to char/narchar if necessary) into single column with comma separator and use union to join all selects together.

  Edited by - davidburrows on 12/15/2003 09:13:52 AM

  Far away is close at hand in the images of elsewhere.
  Anon.

• eletuw

  SSCrazy

  Points: 2464

  More actions

  December 16, 2003 at 4:21 am

  #486347

  solutions 1 and 3 will be my best bet. Can you give me examples of how these can be done?

  Thanks.

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  December 16, 2003 at 5:26 am

  #486368

  bcp "SELECT col1,col2 FROM databasename..tablename" 
  
  queryout file1.csv -S server -u username -P password -c -t "," 
  
  bcp "SELECT col1,col2 FROM databasename..tablename" queryout file2.csv -S server -u username -P password -c -t "," 
  
  copy /A file1.csv+file2.csv file.csv 
  
   

  bcp "SELECT cast(col1 as varchar)+','+col2 FROM databasename..tablename 
  
  UNION 
  
  SELECT cast(col1 as varchar)+','+col2 FROM databasename..tablename" 
  
  queryout filex.csv -S server -u username -P password -c 
  
   

  Far away is close at hand in the images of elsewhere.
  Anon.

• eletuw

  SSCrazy

  Points: 2464

  More actions

  December 16, 2003 at 5:58 am

  #486373

  Thanks for your help.