Trailing nulls in BULK INSERT / enclosed within Quotes

• gk-411903

  Hall of Fame

  Points: 3801

  More actions

  October 6, 2008 at 2:14 pm

  #213711

  Hello Experts

  I am new to this SQL Server stuff. I am trying to use BULK INSERT to insert data. It works well for all column values except the Trailing NULL; Here is the script and data i am using

  SQL:

  create table my_table

  (USERNAME VARCHAR(30)

  ,login VARCHAR(30)

  ,VERSION INT

  ,DATE_CHANGED DATETIME

  ,ACTION_TAKEN VARCHAR(12)

  )

  BULK INSERT my_table FROM 'H:\SQL_DATA\DBA_Backup\my_table.csv'

  WITH

  (

  FIELDTERMINATOR = ','

  ,ROWTERMINATOR = ''

  )

  Data part (my_table.csv)

  CFIS01,oracle,101,10-SEP-2008 07:17:43,LOGOFF

  CFIS01,oracle,101,10-SEP-2008 07:18:09,LOGOFF

  CFIS01,,101,10-SEP-2008 07:18:45,,

  When i select rows the login shows as NULL for the third row correctly but the last column value in the last row shows as a comma.

  Any ideas?

  Thanks in advance

• gk-411903

  Hall of Fame

  Points: 3801

  More actions

  October 6, 2008 at 2:20 pm

  #881327

  One Correction. The ROWTERMINATOR in my posting should be '' (Backslash n); Somehow it got trimmed when i clicked the "Post Reply" button.

• Christian Buettner-167247

  SSChampion

  Points: 13729

  More actions

  October 6, 2008 at 2:44 pm

  #881341

  Try removing the last comma

  Best Regards,

  Chris Büttner

• gk-411903

  Hall of Fame

  Points: 3801

  More actions

  October 6, 2008 at 4:08 pm

  #881375

  Thanks , it worked!! My Bad, i missed something so obvious.

• Christian Buettner-167247

  SSChampion

  Points: 13729

  More actions

  October 6, 2008 at 4:18 pm

  #881379

  Thanks for the feedback, glad I could help.

  Best Regards,

  Chris Büttner