Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: Bulk insert into table with more columns than data within file

    Hey all

    I have a bulk insert situation that would be nice to be able to pull off. I have a flat file with 46 columns that are to go into a table. The table, I want to have a 47th column to be updated later on by means of a stored proc saying if the import into the system was sucessful or not. I have the rowterminator set as '"\n' thinking that would tell SQL to begin on the next row, leaving the importstatus column null but i still receive an error.

    First of all, is this idea possible within this insert statement. Secondly, if so, what would be the syntax to tell the insert statement to skip that particular column. It is the last column listed in the table so it just needs to start on the next row after it inserts the last bit of data in the flatfile.

    If this is not possible, is it possible to bulk insert into a temp table?

    Thanks
    Last edited by tibor; 06-17-07 at 14:29.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you can do this if you specify a format file. read up on bcp in BOL about format files.

    alternatively, you could bulk insert into a staging table or temp table and then do insert/select from there.

    finally, if you can generate the file over again, you can include a null like this (assuming comma separated:

    1,2,3,,5,

    note the extra commas. in this case, a null would be inserted in the 4th and 6th positions.

Posting Permissions

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