db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From david myers <david.myers.scibearsp...@gmail.com>
Subject Re: Custom Bulk Import from CSV
Date Sun, 11 Nov 2012 11:43:55 GMT
Hi,

another quick thought is that your foreign key is restricted, so if 
there is no related key in the table that this value points to, the 
engine would possibly say that you can't have a 'null' value in column b.

I think your best bet would be to use Mamta's suggestion of a temp 
table, then you can build the correct cross reference for your column 
using an < Insert  select > type statement.

The other advantage of the temp table is that you can more easily get 
the values that haven't been correctly inserted using an < outer join > 
between your target table and the temp table, that or you'll have to 
trawl your way through the error message output (or handle it somehow in 
your code).

David



On 05/11/12 21:24, Mamta Satoor wrote:
> Hi,
>
> In addition to Rick's suggestion, I had another suggestion in case the
> table in which you are imprting is not empty. I haven't tried this
> myself but you can create a temporary table with one INTEGER column
> and import the data from the cvs file into that table. Then insert
> into your original table using that temporary table as follows
> INSERT INTO originalTable(B,C)
> 	SELECT (fixedValueForB, *) FROM temporaryTable;
>
> Mamta
>
> On Mon, Nov 5, 2012 at 9:52 AM, Rick Hillegas <rick.hillegas@oracle.com> wrote:
>> On 11/5/12 7:19 AM, Tom Deering wrote:
>>> I have a table that has the following columns:
>>>
>>> INTEGER A, INTEGER B, DATE C, FLOAT D, FLOAT E, FLOAT F, FLOAT G, INTEGER
>>> H, FLOAT I
>>> A is my primary, auto-generated key
>>> B is a foreign key referencing the primary key of another table.
>>>
>>> I have a csv file containing many rows of data for columns C - I that I
>>> would like to import with "CALL SYSCS_UTIL.SYSCS_IMPORT_DATA". All rows of
>>> the csv file correspond to the same value for B. If be is left unspecified,
>>> then I get:
>>>
>>> Column 'B'  cannot accept a NULL value.
>>>
>>> QUESTION: How can I bulk-import the data for columns C - I while
>>> specifying the use of the same value for column B?
>>>
>>> Thanks in advance.
>>>
>>> -Tom-
>>>
>> Hi Tom,
>>
>> The SYSCS_UTIL.SYSCS_IMPORT_DATA procedure lets you customize which columns
>> of your table correspond to which columns in the csv file. It sounds as
>> though column B is declared to be NOT NULL. The following might help you:
>>
>> o Drop the table and recreate it without the NOT NULL constraint on column
>> B.
>>
>> o At the end of the import, bulk update the B column:
>>
>>    update myTable set b = commonValueForB;
>>
>> o Then turn the NOT NULL constraint on:
>>
>>    alter table myTable alter column b not null;
>>
>> Hope this helps,
>> -Rick


Mime
View raw message