BCP

• HBK_4700

  SSCrazy Eights

  Points: 8932

  More actions

  October 3, 2011 at 12:07 pm

  #243968

  Hi,

  I need to export 9 sql server tables data to pipe delimited text file.

  I tried to use import/export wizard, but I can't export more than one table at a time.

  So just wondering is there any BCP script that I can use for export all 9 at a time.

  Appreciate your help.

  Thanks.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 3, 2011 at 12:32 pm

  #1389740

  laddu4700 (10/3/2011)

  ---

  Hi,

  I need to export 9 sql server tables data to pipe delimited text file.

  I tried to use import/export wizard, but I can't export more than one table at a time.

  So just wondering is there any BCP script that I can use for export all 9 at a time.

  Appreciate your help.

  Thanks.

  might need more info...9 separate files, one for each table, or one file with the content of those 9 files?

  if it's 9 files, it's 9 commands. if it's one file, assuming they all have the same strucuture, it would be one command, using queryout, and a query like SELECT [ColumnList] from table1 UNION ALL SELECT [ColumnList] from table2...etc

  --example of the pipe char as a delimiter

  EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT object_name(object_id) As TbLName,name as ColName FROM SandBox.sys.columns ORDER BY object_name(object_id), column_id" queryout C:\Data\Objects.txt -t"|" -c -T '

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• HBK_4700

  SSCrazy Eights

  Points: 8932

  More actions

  October 3, 2011 at 12:44 pm

  #1389751

  9 tables - 9 pipe delimited file, separate for each table