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 <[email protected]> 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 <[email protected]> > 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 <[email protected]> >> 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 >> > <[email protected]> >> > 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 >> >> <[email protected]> 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 <[email protected]> >> >> > 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 >> > >> > > >
