You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Tom Deering <to...@gmail.com> on 2012/11/05 16:19:53 UTC
Custom Bulk Import from CSV
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-
Re: Custom Bulk Import from CSV
Posted by david myers <da...@gmail.com>.
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 <ri...@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
Re: Custom Bulk Import from CSV
Posted by Mamta Satoor <ms...@gmail.com>.
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 <ri...@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
Re: Custom Bulk Import from CSV
Posted by Rick Hillegas <ri...@oracle.com>.
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