BCP import using format file

• Sathish Kumar. Rajaram

  Mr or Mrs. 500

  Points: 581

  More actions

  April 6, 2006 at 3:21 am

  #112476

  Hi

  I need to import data from fixed length flat file to a table using format file. The flat file contains 4 columns and my table contains 6 columns. The table, flat file and format file are given below

  Event Table

  EventID  int  4    NOT NULL

  EventDate smalldatetime 4    NOT NULL

  EventTitle varchar  25   NOT NULL

  EventNotes varchar no 200  NULL

  Load_Date smalldatetime 4    NOT NULL

  Status  varchar  10   NOT NULL

  Data File

  1    01-05-2006Preparation              Documentation for Audit

  2    01-05-2006Audit                   

  3    15-05-2006AuditMeet                Audit Meeting at HO

  4    22-05-2006AuditReview              Audit Review at HO

  BCP Format file

  8.0

  4

  1  SQLCHAR  0  4      ""      1  EventID        SQL_Latin1_General_Cp437_BIN

  2  SQLCHAR  0  10     ""      2  EventDate      SQL_Latin1_General_Cp437_BIN

  3  SQLCHAR  0  25     ""      3  EventTitle     SQL_Latin1_General_Cp437_BIN

  4  SQLCHAR  0  200    "\r\n"  4  EventNotes     SQL_Latin1_General_Cp437_BIN

  When I execute the BCP command, it throws me an error message as

  Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'Status', table 'EventsDB.dbo.Events'; column does not allow nulls. INSERT fails.

  Status will be updated based on the other process, which may vary based on the record. for example for Audit it will be 'A' for preparation it will be 'P', etc. Pls help me.

  Sathish

  S A T ...
  Sathish's Blog

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  April 6, 2006 at 6:57 am

  #631199

  It seems like a pretty straight forward error message... your table won't allow nulls in the Status column but you're trying to add rows from data that contains no status.  You could temporarily disable the constraint (I believe) and import the data but that will simply defeat the whole purpose of the NOT NULL constraint on the Status column.

  One way to get around this is to create a DEFAULT on the Status column... perhaps "Imported" or "Not Processed" would do.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)