[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2549
  • Last Modified:

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
0
pixnewbie
Asked:
pixnewbie
  • 4
  • 3
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
reb73Commented:
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
 
pixnewbieAuthor Commented:
tks for the advice.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now