Hi,

In I-Sql there is the option Data/Import which makes it possible to load
data into a table from a csv file.
If for example the csv file would contain a duplicate row that would violate
a uniquene constraint, you get the option to either stop the import or skip
that row and continue importing.

Is it (using ASA 11.0.1.2044) also possible to achieve the "import into
table from csv file on error skip row" functionality in a different way
using a sql statement that can be called from outside I-Sql?

Regards

Have a look at the LOAD TABLE statement. This is (other than the ISQL
INPUT statement) a "real" SQL statement and can be used in stored
procedures and the like.

HTH
Volker

Am 21.01.2010 19:37, schrieb Christian Hamers:

I already had a look at the LOAD TABLE statement, but I don't think it has
an option like: "on error skip row"

Regards
Christian

"Volker Barth" <No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de> schreef in bericht
news:4b58a5eb$1 (AT) forums-1-dub (DOT) ..

Yes, that is something I've been wanting badly sometimes, but LOAD TABLE
doesn't offer that option, nor does INPUT INTO.

One workaround I use:
select first * into #temp from mytable order by id;
delete #temp;
input into #temp from 'C:\file.csv';
insert into mytable on existing skip
select * from #temp;
drop table #temp;

While this is a little bit kludgy, at least it works.

Regards
Reimer


Christian Hamers wrote:

This will work as long as the insert into fails on a duplicate PK.
But it won't if it fails on a missing FK relationship or a duplicate value
for a unique index.

The biggest problem I have is that I need to import huge amounts of data
into multiple tables. And I also now in advance that those will contain
some records that have missing FK relationships. Until now I have worked
around this problem by inserting the records one by one and if a insert
fails I just skip that record, but this is becoming way to slow and I badly
need to find a faster solution for this.

Any thoughts are welcome

Regards,
Christian

"R. Pods" <r.pods (AT) gmx (DOT) net> schreef in bericht
news:4b596ab6$1 (AT) forums-1-dub (DOT) ..

Christian,

sorry, I missed the "on error skip row" part of your question completely.
Then I have to agree with Reimer that there's no option to skip
errornous rows.
Feel free to post that in the product-futures NG

Volker


Am 21.01.2010 20:57, schrieb Christian Hamers:

Would it be a solution to import data into temp tables (as Reimer
suggested) without the according FKs (and other possibly conflicting
constraints)?
After the import, you could check where FKs would be violated in the
temp tables, and delete/adjust those rows. Then use insert select
(possibly WITH AUTO NAME) to fill the real tables.

(I remember similar difficulties when LOAD TABLE failed because of wrong
data types in a few rows, and I had to load into a local temp table with
just varchar data to find out the failing values. LOAD TABLE is fine and
fast IMHO, but looking for unfitting values seems the hard way.)

Sometimes "LOAD TABLE ... STOP ON ERROR" (instead of a rollback as it
does IIRC) would be useful, too.

Volker

Christian Hamers wrote:

Having load table stop when it gets an error might be technically
possible with row logging turned on but it would come at a performance
penalty. LOAD TABLE gets some of its performance advantage by using what
we call "page-level logging". Rather than adding individual inserts to
the undo log, we just use the checkpoint log if something fails to blast
pages back to how they looked before the LOAD started. That's why
there's a commit & checkpoint to begin with and a rollback if something
fails.

FWIW, stopping at failure might also be considered inconsistent with
some other behaviour. SQL statements themselves either complete or do
nothing. For example, if an INSERT ... FROM SELECT ... failed to insert
a row then the entire INSERT is rolled back. There is no option to leave
the statement partially executed. I'm not saying it's impossible, just
unusual.

-john.
--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

On 1/22/2010 5:38 AM, Volker Barth wrote:

Good points, John - thanks for this clarification.

LOAD TABLE should still be an atomar operation, for sure. I guess it
might still be considered "complete" when an option says "DO AS MUCH
WORK AS POSSIBLE" and LOAD TABLE does just that - i.e. load rows in the
order specified and then commit.

So a "unfitting row" would be more or less like a "end of row" marker. I
see that this, however, won't fit to the page-level logging if the
consistency checks are applied AFTER the load.

Thinking more about that, my approach is primarily focussed on testing
(i.e. checking failed LOAD TABLE statements), and as such, performance
is much less of a concern than ease of use. For thsat reason, INPUT
might fit better (as it uses a bunch of INSERTS internally AFAIK), and
the "stop vs. continue" would also fit better.

However, the syntax and facilities of both statements are quite
different. Therefore, in case of a LOAD TABLE with failing data, it
would not be of much help if one had to rewrite an according INPUT
statement to test further.

Just my 2 cents

Volker

John Smirnios [Sybase] wrote:

In article <4b59d6d7$1@forums-1-dub>, "John Smirnios [Sybase]" says...
But the "ON EXISTING" clause handles many of the issues that would
otherwise cause errors.

D