Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2007
    Posts
    3

    Unanswered: Using BCP to Import

    Hi

    I'm using the BCP facility to import a text file into a database. My problem is, in the table there are 10 fields but my file only contains data for 3 fields

    e.g
    Table - id,forname,surname,dob,gender,mob_number
    File - forname,surname,mob_number

    is there a way of only importing into the required fields or do i have to have columns in the order they are in, in the table????

    can I use this file??
    matt,jones,0775446644
    chris,jones,066565465

    or do i have to use this???
    ,matt,jones,,,0775446644
    ,chris,jones,,,066565465
    Edit/Delete Message

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't think there is!

    An alternative solution may be to BCP the file into a table with 3 columns and then run an INSERT INTO command to put the data into your correct table/columns.
    Code:
    INSERT INTO MyExistingTable('Field2','Field3','Field8')
    SELECT Field1, Field2, Field3
    FROM MyTemporaryTable
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2006
    Posts
    16
    Create a view that take BCP OUT of that view. i think this would solve your problem.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can use format files too but the view is ++ a better idea.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is exactly the kind of problem that BCP format files were created to fix. There is a great article on using BCP format files by Nigel Rivet that ought to help.

    -PatP
    Last edited by gvee; 08-06-07 at 11:44. Reason: tidying up BBcode for URL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    This is exactly the kind of problem that BCP format files were created to fix. There is a great [url=http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html]article[/b] on using BCP format files by Nigel Rivet that ought to help.

    -PatP
    Don't you find format files très difficiles monsiour Phelan? I have not yet looked at the link but they are fiddly little mites IME.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump
    Don't you find format files très difficiles monsiour Phelan? I have not yet looked at the link but they are fiddly little mites IME.
    Keep in mind Pootle what I are old-Phart...

    Format files were all we had in the olden days. If'n what paw wouldn't let us take the wagon and the team, we had to walk to school, 12 milues, up hill both ways!

    On a more serioius note, format files are gnarly at first. They force you to think about data the way that the server has to think about it, but that doesn't bother me much anymore... Now I'm so warped that it seems quite natural.

    -PatP

  8. #8
    Join Date
    Aug 2007
    Posts
    5
    Actually format files are rather simple: see here http://msdn2.microsoft.com/en-us/library/ms191479.aspx
    and they are designed to do exactly what you're looking for.

    and once you have one figured out they're very easy/fast to copy/paste for new requirements. Also you won't encounter all the database logging that you would with straight INSERTs of any kinds - hence the name 'bcp' for 'bulk copy'. With a serious data load, bulk operations are your only option.

    This is a nice reference to the bcp command:
    http://msdn2.microsoft.com/en-us/library/ms191479.aspx


    hth -

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •