Using sde.next_rowid in T-SQL

9423
10
05-30-2017 10:37 AM
SherrieKubis
Occasional Contributor

SQL Server 2016 
ArcGIS 10.5 

I want to insert into a registered table using set processing using sde.next_rowid.  

I do this in Oracle as:
INSERT INTO MYTABLE (OBJECTID, SITE_ID) 
SELECT SDE.GDB_UTIL.NEXT(ROWID ('OWNER','MYTABLE'), SITE_ID FROM MYVIEW; 

In SQL Server ESRI documentation examples I see selecting the objectid, writing down the number, and then hard coding it into the insert statement.  
This will be a nightly job that will insert thousands of rows, so set processing is required. 

I try 
INSERT INTO MYTABLE 
SELECT SDE.NEXT_ROWID ('OWNER','MYTABLE'), SITE_ID
FROM OWNER.MYVIEW; 
and get  
Cannot find either column "SDE" or the user-defined function or aggregate "SDE.NEXT_ROWID", or the name is ambiguous.

I can use SDE.NEXT_ROWID to query like so:
DECLARE @id as integer
EXEC sde.next_rowid 'OWNER', 'MYTABLE', @id OUTPUT;
SELECT @id "Next ObjectID";

and this works just fine.  

I'm new to SQL Server, I'm sure it's a syntax issue. 

Any insights are appreciated. 

Tags (1)
0 Kudos
10 Replies
SherrieKubis
Occasional Contributor

Thanks, Alex, I'll keep this handy. I've been pulled away from this issue and haven't been able to get back to it, but I'd like to try it.  I'll update when I get back to that point. 

0 Kudos