BULK INSERT for text greater than 8000 chars?

• James Millar-305032

  Ten Centuries

  Points: 1047

  More actions

  December 7, 2006 at 5:38 am

  #115390

  Hi guys,

  I am attempting to bulk insert from a .dat file that contains 2 data columns separated by a tab.  The first column is airline code so this will be a simple char - i.e. AA, BA, QF etc.  The second column contains the airline terms and conditions which is a Text field that will exceed 8000 chars for some airlines.  When i attempt to bulk insert in QA where the airline T&C's are greater than 8000 chars i get (0 row(s) affected).

  Is there a way to insert text fields greater than 8000 into my database table or is this simply a limitation of the bulk insert operation in SQL Server 2000?

  Much appreciated,

  James

• john-132685

  SSCertifiable

  Points: 6084

  More actions

  December 7, 2006 at 11:49 am

  #676704

  Maybe.

  Provided your data does not have characters that will confuse BCP (extra carriage return/linefeeds:

  Bulk copy the entire row into one TEXT datatype column staging table

  Then use substring to parse out the first field from the text column when moving into final table.  E.g.:

  INSERT INTO ##test2

     SELECT SUBSTRING( t, 1, charindex( char(9), SUBSTRING( t, 1, 10) ) )

           ,t

       FROM ##test1

  and splice the text column sortalike this:

  update ##test2 SET t = SUBSTRING( t, charindex( char(9), SUBSTRING( t, 1, 10) ) + 1, 4000) + SUBSTRING( t, 4001, 4000)

  (you'd have to include the charindex referencing in each splicing)