0

I'm trying to use BCP to export my data to a .TXT file, but I needed to do an incremental in the same file, since I will need to export data from two views.

Example:

 SET @cmd = '"SELECT * FROM Site"'

 SELECT @sql = 'bcp '+@cmd+' queryout D:\mytest.txt -c -t; -T -S SERVER-PC\SQLEXPRESS';

 EXEC xp_cmdshell @sql;


 SET @cmd = '"SELECT * FROM Customers"'

 SELECT @sql = 'bcp '+@cmd+' queryout D:\mytest.txt -c -t; -T -S SERVER-PC\SQLEXPRESS';

 EXEC xp_cmdshell @sql;

Data out:

 H;04399024100427;20160620
 V;04399024100427;CUSTOMER I;STATE;CITY;NAME;75123390;A

I thought of UNION ALL but the structure of the tables are different and I have cases where I will need to export data from up to 5 tables.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Daniel Andrade
  • 1
  • 1
    You can still create a view to union your tables, just cast all columns to varchar and include placeholders of null or blank strings. – Stu Dec 13 '21 at 01:04
  • The problem is that the table that contains the fewest fields will appear in the delimited field, with the amount of columns existing at the junction of the two tables. – Daniel Andrade Dec 13 '21 at 16:36
  • I managed to solve it by creating 3 files, each according to its table structure and then joining the 3 files. – Daniel Andrade Dec 13 '21 at 17:49
  • 1
    Does this answer your question? [How to export data as CSV format from SQL Server using sqlcmd?](https://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd) – Charlieface Dec 13 '21 at 18:02
  • 2
    T-SQL is not the place for firing off command shells. I'd suggest you use [the Powershell answer there](https://stackoverflow.com/a/23976218/14868997), along with the `-Append` option for `Export-Csv`, this will append to the existing file. – Charlieface Dec 13 '21 at 18:03

0 Answers0