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...@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 <danprice...@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...@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...@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 <danprice...@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 > > > > >
