Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    13

    Unanswered: Select specific columns during bulk insert operation

    Hello,

    My table has 73 columns and text file which I need to upload has 72 comma seperated values for each row.

    When I try to use Bulk Insert to upload this text file the operation fails as there is no value for 73rd column.

    Is there anyway where I can specify the number of columns per row which need to be copied into the sql server table.


    Thank you,

    amit

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about bulk insert in to a work table with 72 columns?

    Or how about using bcp with a format file?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Posts
    13
    Originally posted by Brett Kaiser
    How about bulk insert in to a work table with 72 columns?

    Or how about using bcp with a format file?
    Brett,

    Thanx For your reply.

    The volume of data is very high hence inserting the data in a 72 column table and then transferring it into a 73 column table is not an efficient way of dooing it. Also, the size of log file (Transaction log) will increase if I copy the data from one table and insert another.

    I do have a work around, where I drop the 73rd column using Alter Table Syntax and then add it back after the bulk insert is done.

    I was hoping to get a solution where all this can be avoided if I could specify the number of columns in bulk insert command

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You can use a format file with bulk insert...that should do the trick...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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