BCP datafile load in specific table columns

• SQLIndia

  Ten Centuries

  Points: 1073

  More actions

  December 29, 2004 at 6:32 am

  #162964

  Hi All

  I have this little holdup at BCP data file load in specific table columns andit is like as below:

  Example of data File Record format:-

  1 line...

  59161003|0|0|0|18|144|2004-11-15 11:46:11|2004-11-15 11:48:35|126|0|0|213|6|26|6|-1|-1|40|1|0|1|0|0|0|0|0|0|0|0|0|0|1|2|4|0|1|0|0|1|0|0|0|0|0|0|0|0|0|00000000000000000000|19000|02E19019|20577||41445||20577|3135822658|371576529191009||00||||||

  2 line...

  59161004|0|0|0|16|141|2004-11-15 11:46:14|2004-11-15 11:48:35|125|0|0|213|6|126|6|-1|-1|45|1|0|1|0|0|0|0|0|0|0|0|0|0|1|2|4|0|1|0|0|1|0|0|0|0|0|0|0|0|0|00000000000000000000|19000|03A18023|22757||41516||22757|5099255577|373275350951007||00||||||

  table structure:-

  id int (identity)

  data varchar (1024)

  Now i would like to BCP data file records in data column and id column populated by itself cos it is identity column.. but when i use normal BCP command

  FOR %%f IN ("H:\echdata_sourcefiles\GSO\11152004\*") DO call :IMPORT %%f GSO

  :IMPORT

  set Filename=%1

  echo %filename%

  BCP DBMY..import_table in %Filename% -Sservername -Uusername -Ppword -c -t"|"

  it insert the first section of data file (59161003) goes into first column id and rest of data goes into data column so i would like to create format bcp structure so i can insert whole record from data file goes into data column....

  Thanks in advance..

  sonny

  singhsonny@hotmail.com

  SqlIndia

• THANG HOANG

  SSChasing Mays

  Points: 632

  More actions

  December 30, 2004 at 4:31 pm

  #535476

  Your BCP format file should look something like this.

  (Substitute the appropriate SQL version number and collation name)

  8.0

  1

  1 SQLCHAR 0 1024 "\r\n" 2 data SQL_Latin1_General_CP1_CI_AS

• SQLIndia

  Ten Centuries

  Points: 1073

  More actions

  December 30, 2004 at 10:41 pm

  #535487

  Thanks Thang...

  really appreciate your help ... it's work perfectly 

  SqlIndia

• SQLIndia

  Ten Centuries

  Points: 1073

  More actions

  January 4, 2005 at 2:52 am

  #535731

  Hi

  suppose if i have another field in same table and want to pass value to this field from BCP format file...(or another way through BCP) is this possible???

   if yes then could some one help me..I have tried but no luck so far..

  Thanks in advance..

   

  SqlIndia