what will be the best delimiter for bcp file? Dont want to use , |,^. Any suggestion will be appreciated

• Suhan

  SSC Veteran

  Points: 202

  More actions

  November 19, 2013 at 11:19 am

  #395854

  We are migrating dbs from sql server to oracle. I have 50+ table with million + rows. I was going to bcp out those tables using bat file. Can anyone suggest best delimiter?

• Lowell

  SSC Guru

  Points: 323504

  More actions

  November 19, 2013 at 11:25 am

  #1667516

  don't limit yourself to think it must be a single char.

  i export stuff like html in and out all the time; as a result, i use multi character delimiters that I'm sure won't exist in the data.

  the advantage is it preserved Carriage Return Line feeds that exist in the data.

  here's an example:

  --using a super special 4 char row delimiter to be sure it doesn't exist in the data

  --flags explanation:

  -- -c = character data

  -- -t"[||]" = field terminator

  -- -r"[~~]" = row terminator

  -- -T' = Trusted connection

  --out

  EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT EMAILTEMPLATESID, EMAILBODY FROM BFONRA.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID" queryout c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

  --in

  EXECUTE master.dbo.xp_cmdshell 'bcp BFONRA.dbo.EMAILTEMPLATES in c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

  --in via bulk insert

  BULK INSERT EMAILTEMPLATES FROM 'c:\Data\bcpExample.txt'

  WITH (

  DATAFILETYPE = 'char',

  FIELDTERMINATOR = '[||]',

  ROWTERMINATOR = '[~~]',

  FIRSTROW = 1

  )

  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!

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  November 19, 2013 at 12:02 pm

  #1667529

  The best delimiter will be the one that doesn't exist in your data.

  Only you can find that.

  Lowell's suggestion is a good one.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]