groovy-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Durham <david.durham...@gmail.com>
Subject Re: Batch Insert with Sequence?
Date Wed, 02 Dec 2015 04:16:44 GMT
OK, so if you're not using the database sequence, then you don't need
the NEXT VALUE FOR function.

On Tue, Dec 1, 2015 at 12:09 PM, Daniel Price <danprice303@gmail.com> wrote:
> 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