CODEWITHSUNDEEP
sqlsql-serveroracle-databaseoracle10glinked-server
stringpoet
stringpoet

Reputation: 164

Selecting a sequence NEXTVAL for multiple rows

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

Upvotes: 1

Views: 4775

Answers (3)

Mohamed Abulnasr
Mohamed Abulnasr

Reputation: 597

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

Upvotes: 0

W. Nema
W. Nema

Reputation: 329

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

Upvotes: 3

stringpoet
stringpoet

Reputation: 164

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.

Upvotes: 0

Related Questions