Selecting a sequence NEXTVAL for multiple rows

Question

I am building a SQL Server job to pull data from SQL Server into an Oracle database through a linked server. The table I need to populate has a sequence for the name ID, which is my primary key. I'm having trouble figuring out a way to do this simply, without some lengthy code. Here's what I have so far for the SELECT portion (some actual names obfuscated):

SELECT (SELECT NEXTVAL FROM OPENQUERY(MYSERVER, 
    'SELECT ORCL.NAME_SEQNO.NEXTVAL FROM DUAL')), 
     psn.BirthDate, psn.FirstName, 
     psn.MiddleName, psn.LastName, c.REGION_CODE
FROM Person psn
LEFT JOIN MYSERVER..ORCL.COUNTRY c ON c.COUNTRY_CODE = psn.Country

MYSERVER is the linked Oracle server, ORCL is obviously the schema. Person is a local table on the SQL Server database where the query is being executed.

When I run this query, I get the same exact value for all records for the NEXTVAL. What I need is for it to generate a new value for each returned record.

I found this similar question, with its answers, but am unsure how to apply it to my case (if even possible): Query several NEXTVAL from sequence in one statement

How is this `select` query transferring data into an Oracle database? — Andomar, Nov 19 '12 at 17:58
It's just doing a simple insert. I didn't include that part of the code because I didn't feel it to be necessary to the question. — stringpoet, Nov 19 '12 at 18:02
I don't believe I said `into` anywhere in the question. EDIT: I see where you're confused. Yes, it will ultimately insert into an Oracle table, but I only posted the `select` portion because that's what I need to know for now. The `insert` is irrelevant. — stringpoet, Nov 19 '12 at 18:04
A quick scan or search would have yielded "from SQL Server ***into*** an Oracle database" (emphasis mine). — Aaron Bertrand, Nov 19 '12 at 18:05
I'm not sure why the rudeness is warranted. I very clearly stated that I was posting the `SELECT` portion of the code. The only thing I'm concerned about is getting a new sequence value for all returned records. — stringpoet, Nov 19 '12 at 18:08
I bolded the most important part of the question. Hopefully this will help. — stringpoet, Nov 19 '12 at 18:16
What rudeness are you talking about? Andomar asked how your query was selecting *into* an Oracle database. I was merely explaining why they might have come to that conclusion. Saying "I don't believe I said `into`" is pretty obnoxious when it's in the first line of the question, isn't it? — Aaron Bertrand, Nov 19 '12 at 18:27
@AlexanderFedorenko it's not. http://en.wikipedia.org/wiki/DUAL_table — Aaron Bertrand, Nov 19 '12 at 18:29
I didn't intend to be rude with that comment; I apologize if that's what was taken by it. I've found one way to do this by using a table on the Oracle side of things and selecting the sequence value from that, but that's unfortunately not an option, as the table I'm selecting from is on the SQL Server, and I have to `OPENQUERY` the Oracle db for the sequence number. — stringpoet, Nov 19 '12 at 18:32

score 3 · Answer 1 · edited May 15 '13 at 19:13

3

put it in a SQL scalar function. Example:

CREATE function [dbo].SEQ_PERSON() 
returns bigint as
begin
    return
    (   select NEXTVAL 
        from openquery(oraLinkedServer, 'select SEQ_PERSON.NEXTVAL FROM DUAL')
    )
end

edited May 15 '13 at 19:13

Jack Pettinger

2,715
1
23
37

answered May 15 '13 at 18:54

W. Nema

189
1
8

score 0 · Accepted Answer · answered Nov 21 '12 at 15:42

0

I ended up having to iterate through all the records and set the ID value individually. Messy and slow, but it seems to be the only option in this scenario.

answered Nov 21 '12 at 15:42

stringpoet

164
4
14

score 0 · Answer 3 · answered Jan 14 '18 at 15:56

Very easy Just Use a CURSOR to Iterate with the code :

SELECT NEXTVAL AS SQ from OPENQUERY(MYSERVER, 'SELECT  AC2012.NAME_SEQNO.NEXTVAL FROM DUAL')

So you can embed this select statement in any Sql statement, and Iterate by the CURSOR.

PS:

DECLARE SQCURS CURSOR  
FOR SELECT (SELECT NEXTVAL AS SQ FROM OPENQUERY(MYSERVER, 
'SELECT ORCL.NAME_SEQNO.NEXTVAL FROM DUAL')), 
 psn.BirthDate, psn.FirstName, psn.MiddleName, psn.LastName, c.REGION_CODE

FROM Person psn LEFT JOIN MYSERVER..ORCL.COUNTRY c ON c.COUNTRY_CODE = psn.Country

OPEN SQCURS FETCH NEXT FROM SQCURS ;

I hope that help

Selecting a sequence NEXTVAL for multiple rows

3 Answers3