Inserting constant column values using BULK INSERT

Question

Post reply

Inserting constant column values using BULK INSERT

Joe Wagner

Ten Centuries

Points: 1394
More actions
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

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

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

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

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.

Joe Wagner Ten Centuries Points: 1394 More actions · Answer 2

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.

klawrence SSC Veteran Points: 259 More actions · Answer 3

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

Joe Wagner Ten Centuries Points: 1394 More actions · Answer 4

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

klawrence SSC Veteran Points: 259 More actions · Answer 5

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

Joe Wagner Ten Centuries Points: 1394 More actions · Answer 6

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?

Inserting constant column values using BULK INSERT

Cookies on SQLServerCentral