how to hardcode a column value during bulk insert

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?
tks
pixnewbieAsked:
Who is Participating?
 
reb73Connect With a Mentor Commented:
DTS would be comparable to BULK INSERT as the DTS data-pump at its simplest form uses the same underlying logic as BULK INSERT..

0
 
reb73Commented:
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..




0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
reb73Commented:
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..
0
 
pixnewbieAuthor Commented:
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?

tks
0
 
reb73Commented:
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?
0
 
pixnewbieAuthor Commented:
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.
0
 
pixnewbieAuthor Commented:
tks for the advice.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.