Post reply

Inserting constant column values using BULK INSERT

  • March 19, 2004 at 12:34 pm

    #161124

    Is it possible to insert a constant value (not contained in the input file) into all rows of the target table of a BULK INSERT operation?

    In our scenario, there are multiple threads (stored procedures) inserting into a common staging table, with the only differentiating element being a department id.  This id is known to the stored procedure, but is not contained in the source CSV file.

    Thanks,

    Joe

  • March 19, 2004 at 12:47 pm

    #499567

    I don't think you can.

    You should bulk insert into a temp table, and then insert into the staging table adding the department id.

  • March 19, 2004 at 2:29 pm

    #499589

    Thanks.

    We have a workaround already which involves the pre-processing of the original CSV file to prepend the dept  id to each row.  I was hoping for a way around the preprocessing.

  • March 19, 2004 at 3:07 pm

    #499597

    If the table is already defined, try putting a default value on the column.

  • March 19, 2004 at 3:12 pm

    #499598

    Given that the dept id can vary from file to file, how would this work?

  • March 19, 2004 at 3:21 pm

    #499602

    In that case a DTS package would probably be you best bet.

  • March 19, 2004 at 3:34 pm

    #499606

    Do you know what the performance differences are between invoking a DTS package from T-SQL, and using SP_EXECUTESQL BULK INSERT...?

    Also, are DTS packages re-entrant, since there will be multiple threads attempting to execute the same package?

Viewing 7 posts - 1 through 6 (of 6 total)

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