How to validate text file data using bulk insert

• SAG2011

  SSC Enthusiast

  Points: 179

  More actions

  May 17, 2012 at 1:00 am

  #255993

  Hi,

  I want to import data from text file.format of file is as follows:

  date,id,unit_number,Quantity

  05/17/2012,1,117,100

  05/16/2012,2,118,100

  I am using bulk insert do it.like this

  BULK

  INSERT vw_bulk_insert_test

  FROM '\\server\c$\csvtext.txt'--\\server\SQLEXPRESS\csvtest.txt'

  WITH

  (FIRSTROW=2,

  check_CONSTRAINTS,

  FIELDTERMINATOR = '~',

  ROWTERMINATOR = ''

  )

  GO

  But before insert I want to validate values of each column.Like if second row will have values of all fields except unit_number then it should throw an error that unit_number value is missing.And it should not insert ant data after that.

  Please guide me on the same.

  Thanks in advance.

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  May 17, 2012 at 8:50 am

  #1489072

  How many validations, just missing unit_number? You might be able to set the column to NOT NULL and have BULK INSERT ignore errors.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato