Post reply

Using a variable as a field value in a bulk insert

  • December 29, 2004 at 8:21 am

    #60585

    All-

    We have a process that generates a text file for loading into a SQL table called Files.  Each time we load this data, we first put an entry into a BatchLoad table tracking when the load was started along with some other information.  The table has an identity column, and we grab this @@IDENTITY and use it to link the rows loaded into Files.  Right now we're using OLEDB's text file driver to create a linked server as described in http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_8gqa.asp. 

    The code sort of looks like:

    INSERT INTO BatchLoad (LoadDate) VALUES(GetDate())
    SELECT @BatchID = @@IDENTITY
    INSERT INTO Files
      SELECT @BatchID, fi.FileName, fi.FileSize
      FROM Files...[Listing#txt] fi

    This generally works OK, but OLEDB has a limitation of 255 characters per field and we from time to time run into this problem.  Furthermore, this clearly isn't the best performing approach and any errors will cause the whole insert to roll back (unlike BULK INSERT's BatchSize argument).

    There are several alternative options for doing this, but I don't seem to be able to find any that lets us include the @BatchID as a field value for each row.  I suppose we could insert the rows with NULL as a value and do some post-processing to update WHERE NULL, but this is clunky and isn't really atomic without some additional logic. 

    What's the best approach to perform this insert from a text file and have this variable value applied to each row?

    Thanks,

    Mark Denner

  • December 29, 2004 at 8:40 am

    #535279

    Good question. I'd choose the clunky approach since it's simpler (to me) and you have a loaddate. If the update fails, you could remove the rows easily.

     

  • December 29, 2004 at 5:44 pm

    #535361

    I would load the data into a staging table with bulk insert, then insert the date into the batch table, and finally insert the data from the staging table into your Files table with the identity value from the batch table.  BTW, I would use SCOPE_IDENTITY().  From BOL:

    SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

    Luck, Dave

    There is no "i" in team, but idiot has two.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply