nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using sde.next_rowid in T-SQL

SherrieKubis

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.

Find more posts tagged with

Accepted answers

All comments

MAGICMuscatineAreaGeographicIn

Yes that was always our rule of thumb too. We had some old sql DBAs that tried to back door GIS and really screwed things up. That was probably 15 years ago and before using sql geometry. I think using sql geometry has really opened the door to opportunities. The reason that we had to do this was because there is such an overhead with ESRI joins using map services and the operations dashboard. We are using the operations dashboard to view electrical outage information coming from our CIS system. There are multiple widgets on our dashboard updating every 2 minutes. When doing the join through esri the update was happening on all 7 widgets every 2 minutes and our sql server (server) was overwhelmed.

One good thing I guess is not having to recover this information as it is updated every 2 minutes and the only thing that comes from esri is the shape of the points. Doing the join once on the sql side and writing the data to a feature class has optimized the operation dashboard greatly and reduced overhead. Doing the join with esri was trying to match 12,000 meters in 7 different widgets every 2 minutes and was a huge bottleneck.

Hopefully it will continue to run and not cause issues, I can't believe they don't have a sql function that will operate like oracle. We do not have to edit the data so unregistering as versioned removes a lot of the interdependence. I guess if we have to we can try to create the ObjectID using an iterator and a loop, but adds a lot of logic over what we currently have in place.

SherrieKubis

Hi Mark. I'm curious to see how this works in the long term. I'm coming from Oracle, where updating the objectid is easier because the next_rowid is a function. We've been using SDE there for a very long time, and from experience, my biggest rule is to never mess with SDE from SQL, always use SDE to do the work. There are so many inter-dependencies in the GDB and metadata tables that you may think you're okay, and then all of a sudden realize an issue is cropping up. I used to try and go around SDE for performance and ease of use, but vowed to abandon that idea after we had to recover the database back to a point-in-time before I touched it. I don't have enough experience with SDE and MS SQL yet to comment, but I'm interested in what others have to say.

Sherrie

SherrieKubis

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.

MAGICMuscatineAreaGeographicIn

Hello All

I just started looking into this as we are starting to try some new things here using sql server to create new features. We did some experimenting last week and we were able to get something to work. Once I started reading some documentation I am a little concerned that we may get into trouble in the future. What we are doing is creating an inner join in sql using a table and a feature class. We then output the matching records to a non-versioned feature class using the shape from the feature class used in the join. We are using a recid identity field to write into the objectid of the output feature class. What we do is truncate the output feature class before inserting the new records. It has been running every 2 minutes for the last week and seems to be functioning like we want. Everything I read says to not mess with the ObjectID, but the workaround using "next_rowid" seems a little much if we are using an Identity field from another table. Has anyone experienced what may happen when you insert values into an objectid of a non-versioned feature class if it is truncated prior to the insert?

AlexMagruder

I found a work around in another post that I can't find anymore. Basically you create a temporary table to store the OBJECTIDS, perform a join with your table, and then perform the insert. This approach works well for a small number of records but becomes slow for larger sets (>100,000). I use this approach often so if you can think of improvements please share.

------------------------------

declare @OBJECTID int
declare @ids table(
OBJECTID INT
)
declare @RowCnt int
declare @MaxRows int

select @MaxRows = count(*) from INPUT_TABLE
select @RowCnt = 1

while @RowCnt <= @MaxRows
begin
   EXEC sde.next_rowid 'OWNER', 'TABLENAME', @OBJECTID OUTPUT
   insert into @ids (OBJECTID) values(@OBJECTID)
   Select @RowCnt = @RowCnt + 1
end

insert into TARGET_TABLE(OBJECTID, COLUMN1, COLUMN2, COLUMN3)
select
   ids.OBJECTID,
   COLUMN1, COLUMN2, COLUMN3
from (
   select
      ROW_NUMBER() OVER(ORDER BY (select null)) AS RowNumber,
      COLUMN1, COLUMN2, COLUMN3
   from INPUT_TABLE
) as attr
join (
   select
      *,
      ROW_NUMBER() OVER(ORDER BY [objectid]) AS RowNumber
   from @ids
) as ids

on attr.RowNumber = ids.RowNumber

bixb0012

Looking back, I forgot to ask, is this versioned data? You said it is registered but didn't explicitly say whether it was versioned or not. If it is versioned, then you can use the versioned view to do the inserts and it does the OBJECTID for you.

SherrieKubis

Joshua,

You example worked great for the first row. For example, the sde.next_rowid returns as 30279. The first row entered has an object id of 30279. The insert statement has 33,000 rows to add, but this doesn't get the next row id, it uses objectid. So it would have to be in a loop, as shown in this article:

How To: Insert geometry from XY coordinates using SQL

But I don't want a loop, I want set processing as opposed to iterative.

For example, in Oracle the statement

INSERT INTO MYTABLE (OBJECTID, SITE_ID)
SELECT SDE.GDB_UTIL.NEXT(ROWID ('OWNER','MYTABLE'), SITE_ID FROM MYVIEW;

Inserts into MYTABLE the uniqueid from next_rowid for all 33,000 rows.

SherrieKubis

Hi Joshua, thanks for the answer. I've been out of the office and am now getting back to this, I'll give this a try and let you know. This is the 10.5 documentation that I was referring to, but your example may help for sure.

bixb0012

The example in the documentation isn't saying, or even suggesting, you write the value down and then hard wire it into a query. The example is trying to demonstrate that you need to retrieve the value first before using it later in a query.

What about:

<SPAN class="keyword token">DECLARE</SPAN> <SPAN class="variable token">@myval</SPAN> <SPAN class="keyword token">int</SPAN>
<SPAN class="keyword token">EXEC</SPAN> sde<SPAN class="punctuation token">.</SPAN>next_rowid <SPAN class="string token">'OWNER'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'MYTABLE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="variable token">@myval</SPAN> OUTPUT

<SPAN class="keyword token">INSERT</SPAN> <SPAN class="keyword token">INTO</SPAN> MYTABLE
<SPAN class="keyword token">SELECT</SPAN> <SPAN class="variable token">@myval</SPAN><SPAN class="punctuation token">,</SPAN> SITE_ID
<SPAN class="keyword token">FROM</SPAN> OWNER<SPAN class="punctuation token">.</SPAN>MYVIEW<SPAN class="punctuation token">;</SPAN> <SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.