batch insert if not already there

[Tim Pigden]

Hi

I've got a table that has a candidate key (a, b) and an integer key (id) - the latter is needed for compatibility with the data warehousing software.

I have a few hundred records that need to be inserted only if record with candidate key (a, b) does not already exist in the database. Is there a slick way of doing this?

I'm torn between inserting into a temporary table and then running a sql query or looking for the records one at a time - but is there something else i could do?

[Konstantinos Kougios]

Ah, the hard issue of merging in-database data with new/changed data.

Do you just have to insert non-existing data? So no updates? In that
case indeed it might be better to use a temp table with just (a,b) (or
if your dataset is small, just load all of them in memory and compare).
This will be the fastest.

But also you can check one by one the new data to see if (a,b) already
exists (using SelectConfig.skipLoading to avoid loading related data as
you need only (a,b)). This way you can find the new data and then do a
batch insert.

> --
> You received this message because you are subscribed to the Google
> Groups "mapperdao" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to mapperdao+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

[Tim Pigden]

Hi

I realised I could quickly query (a, b) by another field - so my set is small. So I'm using jdbc to load a set of (a, b) and then filtering them. Can I do completely ad hoc queries in mapperdao? 

To unsubscribe from this group and stop receiving emails from it, send an email to mapperdao+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "mapperdao" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mapperdao+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--

Tim Pigden
Optrak Distribution Software Limited
+44 (0)1992 517100
http://www.linkedin.com/in/timpigden
http://optrak.com
Optrak Distribution Software Ltd is a limited company registered in England and Wales.
Company Registration No. 2327613 Registered Offices: Suite 6, Hoe Lane, Ware, SG12 9LR England 
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Optrak Distribution Software Ltd. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error.

[Konstantinos Kougios]

well, yes, by using com.googlecode.mapperdao.jdbc.Jdbc#queryForXXX

On 23/08/13 15:55, Tim Pigden wrote:

Hi

I realised I could quickly query (a, b) by another field - so my set is small. So I'm using jdbc to load a set of (a, b) and then filtering them. Can I do completely ad hoc queries in mapperdao? 

On 23 August 2013 15:39, Konstantinos Kougios <kostas....@googlemail.com> wrote:

Ah, the hard issue of merging in-database data with new/changed data.

Do you just have to insert non-existing data? So no updates? In that case indeed it might be better to use a temp table with just (a,b) (or if your dataset is small, just load all of them in memory and compare). This will be the fastest.

But also you can check one by one the new data to see if (a,b) already exists (using SelectConfig.skipLoading to avoid loading related data as you need only (a,b)). This way you can find the new data and then do a batch insert.

On 23/08/13 14:16, Tim Pigden wrote:

Hi
I've got a table that has a candidate key (a, b) and an integer key (id) - the latter is needed for compatibility with the data warehousing software.

I have a few hundred records that need to be inserted only if record with candidate key (a, b) does not already exist in the database. Is there a slick way of doing this?

I'm torn between inserting into a temporary table and then running a sql query or looking for the records one at a time - but is there something else i could do?
--
You received this message because you are subscribed to the Google Groups "mapperdao" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mapperdao+...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "mapperdao" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mapperdao+...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--

Tim Pigden
Optrak Distribution Software Limited
+44 (0)1992 517100
http://www.linkedin.com/in/timpigden
http://optrak.com
Optrak Distribution Software Ltd is a limited company registered in England and Wales.
Company Registration No. 2327613 Registered Offices: Suite 6, Hoe Lane, Ware, SG12 9LR England 
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Optrak Distribution Software Ltd. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error.

--
You received this message because you are subscribed to the Google Groups "mapperdao" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mapperdao+...@googlegroups.com.