groovy-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Price <danprice...@gmail.com>
Subject Re: Batch Insert with Sequence?
Date Tue, 01 Dec 2015 18:09:28 GMT
Not in my use case.  I basically extract a whole table of data into a list,
perform some manipulations/transformations/translations, then batch insert
that list into a DB.  What I've been doing works well and is fast--up to
this point since I've just started using sequence values.  I'm converting
the data in one DB into another DB and I'm trying to use sequence values as
unique keys (I need several per table) to form linking relationships that
don't exist in the client DB but are expected by the destination DB--the
DBs have very different structures. The alternative, and what I've done to
this point, is to use DB views and intermediate tables to put the source DB
into the same structure as the destination DB, but that takes a long time
and none of the effort can be reused.

On Tue, Dec 1, 2015 at 12:25 PM, David Durham <david.durham.jr@gmail.com>
wrote:

> What list of values?  The sequence values are from the database
> itself, and are not fed into the batch by your Groovy code.
>
> On Tue, Dec 1, 2015 at 7:13 AM, Daniel Price <danprice303@gmail.com>
> wrote:
> > Thanks, David!  I read many MSDN pages but didn't encounter that exact
> > syntax.  I'll give it a try and report back.  Since I'm using this insert
> > statement with withBatch(), I wonder how I should handle the list of
> values
> > that are being fed into it...
> >
> > On Mon, Nov 30, 2015 at 11:41 PM, David Durham <
> david.durham.jr@gmail.com>
> > wrote:
> >>
> >> Ah, no, it looks like it's example E. from this page:
> >>
> >> https://msdn.microsoft.com/en-us/library/ff878370.aspx
> >>
> >> INSERT Test.TestTable (CounterColumn,Name)
> >>     VALUES (NEXT VALUE FOR Test.CountBy1, 'Syed') ;
> >>
> >> On Mon, Nov 30, 2015 at 10:38 PM, David Durham
> >> <david.durham.jr@gmail.com> wrote:
> >> > I think your insertString will have something like "insert into
> >> > myTable (...) values (mysequence.nextval, ?,?,?)"
> >> >
> >> > On Mon, Nov 30, 2015 at 9:33 AM, Daniel Price <danprice303@gmail.com>
> >> > wrote:
> >> >> Hi all.  I've been using Groovy to batch insert data into SQL Server,
> >> >> and it
> >> >> works very well.  The syntax I've been using is:
> >> >>
> >> >> //batch insert
> >> >> int dbThrottle = 25000
> >> >> db.withTransaction{
> >> >>    def result = db.withBatch(dbThrottle, insertString){ ps ->
> >> >>       output.each{
> >> >>          ps.addBatch(it)
> >> >>        }
> >> >>    }
> >> >> }
> >> >>
> >> >> This code is used to insert data from list 'output' into my Sql
> Server
> >> >> DB.
> >> >> The 'insertString' is just the typical insert statement dynamically
> >> >> derived
> >> >> from the target table and columns.
> >> >>
> >> >> I now have a need to use a sequence generator to populate one or more
> >> >> columns in some tables.  I can do this by putting sequence numbers
> in a
> >> >> list
> >> >> and inserting such sequence lists into my 'output' data list, but
> this
> >> >> is
> >> >> very slow.  Is there a way I can include the 'NEXT VALUE FOR'
> sequence
> >> >> query
> >> >> in the batch insert query so that it is evaluated during batch
> insert?
> >> >> Will
> >> >> this be any faster?
> >> >>
> >> >> Thanks!
> >> >> D
> >
> >
>

Mime
View raw message