BCP help

• Helen-84411

  Hall of Fame

  Points: 3320

  More actions

  February 21, 2005 at 10:53 pm

  #65659

  I have a table

  Create table Valuation

  (

   nbr_valuation varchar(11) null,

   cod_collectorate varchar(3) null,

   dat_effective datetime null,

   typ_status char(1) null,

   cod_maker varchar(10) null,

   dat_maker datetime null,

   cod_checker varchar(10) null,

   dat_checker DATETIME null,

   dat_created datetime null

  )

  I want to import data into this table from  a text file as shown below

  106A1Z03001|011||R|||lvsoper|2004-04-29 19:58|

  106A1Z03006|011||R|||lvsoper|2004-03-07 19:58|

  106A1Z11011|011||R|||lvsoper|2004-03-07 19:58|

  106A1Z11012|011||R|||lvsoper|2004-03-07 19:58|

  106A1Z11013|011||R|||lvsoper|2004-03-07 19:58|

  106A1Z11014|011||R|||lvsoper|2004-03-07 19:58|

  106A1Z11015|011||R|||lvsoper|2004-03-07 15:53|

  106A1Z11016|011||R|||lvsoper|2004-04-29 01:49|

  106A1Z11017|011||R|||lvsoper|2004-04-29 01:49|

  106A1Z11018|011||R|||lvsoper|2004-03-07 19:58|

  106A1Z11019|011||R|||lvsoper|2004-03-07 19:58|

  106A1Z11020|011||R|||lvsoper|2004-03-07 15:53|

  106A1Z11021|011||R|||lvsoper|2004-03-07 19:58|

  106A1Z11022|011||R|||lvsoper|2004-03-07 19:58|

  106A1Z11023|011||R|||lvsoper|2004-03-07 19:58|

  106A1Z11024|011||R|||lvsoper|2004-04-29 01:49|

  For this I use a format file

  8.0

  9

  1       SQLCHAR       0       11      "|"                       1     nbr_valuation        SQL_Latin1_General_CP1_CI_AS

  2       SQLCHAR       0       3       "|"                       2     cod_collectorate     SQL_Latin1_General_CP1_CI_AS

  3       SQLCHAR       0       8       "|"                       3     dat_effective        SQL_Latin1_General_CP1_CI_AS

  4       SQLCHAR       0       1       "|"                       4     typ_status           SQL_Latin1_General_CP1_CI_AS

  5       SQLCHAR       0       10      "|"                       5     cod_maker            SQL_Latin1_General_CP1_CI_AS

  6       SQLCHAR       0       8       "|"                       6     dat_maker            SQL_Latin1_General_CP1_CI_AS

  7       SQLCHAR       0       10      "|"                       7     cod_checker          SQL_Latin1_General_CP1_CI_AS

  8       SQLCHAR       0       20      "|"                       8     dat_checker          ""

  9       SQLCHAR       0       20       "\r\n"                   9     dat_created          SQL_Latin1_General_CP1_CI_AS

  But I get an error. I could understand why this is so (because of 2004-03-07 19:58 format) If this is some thing like 2004-03-07 11:58 it gives no error. If I use SQLdatetime in format file instead of SQLchar for 8th Field  it gives me a negative date

  command :

  exec master..xp_cmdshell 'bcp dba_db..Valuation in "D:\TEsting\Roll_main.txt" -fD:\TEsting\Roll_main.fmt -q -Stest -T'

   

  Can anyone explain why this is so.

  
  Helen
  --------------------------------
  Are you a born again. He is Jehova Jirah unto me

• philcart

  SSC-Forever

  Points: 47797

  More actions

  February 21, 2005 at 11:25 pm

  #543405

  Do you have to use BCP? DTS has a builtin Datetime transformation.

  Is the destination table the final destination, or is it a staging table. If it's not a staging table, and you have to use BCP, create a second table and set all the fields to varchar. Then create a stored procedure to handle the converts between the tables.

  And finally, if you can't use DTS, use BULK INSERT instead of BCP.

   

   

   

  --------------------
  Colt 45 - the original point and click interface