We help IT Professionals succeed at work.

how to hardcode a column value during bulk insert

pixnewbie asked
Medium Priority
Last Modified: 2012-05-06
I need to do an import where one of the cols isnt in the source file but for a given import, its the same value. I'm "unflatening" a file into related tables and for some fo the columns, I need to basically rotate the data such that the values go into one col and the "typeID" is related to the original col it come from so defaulting the tgt table isnt an answer as mentioned in http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21735262.html.  I truely want to hardcode a value into the format file. Otherwise I need to do some intermediate work in one or more working tbls. I'd like to skip that step if possible.   Possible?
Watch Question

You cannot hardcode a static value in the BCP format file.

If a column has a default value for a given import, add an ISQL/OSQL batch command before the BCP command, changing (drop default constraint if exists, then create new default constraint with value required), the default constraint of this particular column to the value required. Then omit the column in the BCP format file..

The preferred and efficient way would still be to have this unavailable column defined as a NULLable field in the destination table and use a post-bcp update (can be just an ISQL/OSQL command with a single update statement) to update all NULL values in this column to the value required..

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Thinking in the other way around, Alter that table to have that particular table to hold some default value.
So while loading, if you dont have any values, then your default value will gets loaded into that table.
Hope this satisfies your requirement.

rrjegan17, the poster has expressly indicated that a static default in the column is not an option, with the solution you have suggested identical to the answer in the EE link in the question and not being suitable..


I wouldnt want to loosen my constraint by using nullable since that would weaken other inserts on the "live" table. So I'm thinking I bulk insert into a working table, fill in the foreign keys,e tc there then insert... select into the "live" tbl. Not as blazingly fast but far better than individual inserts. ;) I dont have to do this often but when I do, I dont want to pull an all-nighter.

Sound reasonable to you guys? Any opinion about the working tbls being in the live db vs. an entirely different working db?


Bulk Insert into an intermediate table sounds feasible and safe and will allow you to manipulate the value to be populated for the default column in the final table..

If an identity column is available in the target table, you could also look at populating directly to the target table minus the column not available, and do an update using maximum rowid before update as a starting point

Have you contemplated using DTS/SSIS which support derived columns and can provide a solution without the use of intermediate tables?


I actually have a multi-step process where I insert the parent tbl to establish the identity, match map the external identity with my identity to be foreign keys in various child tables. And I'm not that good with DTS. :) How performance of bulk insert vs. DTS?  Might be worth learning.
DTS would be comparable to BULK INSERT as the DTS data-pump at its simplest form uses the same underlying logic as BULK INSERT..

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


tks for the advice.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.