topic Re: How to do bulk insert into Feature class from SQL - ObjectID's!! in ArcGIS API for Flex Questions

How to do bulk insert into Feature class from SQL - ObjectID's!!

grahamcooke — Tue, 25 Oct 2011 09:09:43 GMT

Apologies if this is in the wrong place, but i wasnt sure where to put it.

I am trying to populate a featureclass from within a sqlserver stored procedure, that i am calling via a webservice from within flex.

The reason i am doing this rather than a query task, is that the stored procedure brings in time data from a source outside of SDE. The idea is that I will have a mapservice that uses this feature class joined to a feature class that contains the geometry of the polygons i need to return. I can then create a featurelayer on the fly and use a featurecollection to create it (as per the GPX sample on the flexapi site).

The problem I am having is how to create the ObjectID's

I found this link:http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/in_SQL_Server/006z00000017000000/

but obviously this only works for 1 row. My stored procedure could potentially be creating 800+ rows:

INSERT INTO gisadmin.MonthBookings_Temp

SELECT [objectid], t.GP22_AssetNumber, m.site_name, m.VolDate, MAX(Actual_Users) AS [Actual_Users]

FROM cawdapt.synIRISWarehouse m, gisadmin.OTTERBURN_GP22TRAININGAREAS t

WHERE m.facility_category collate Latin1_General_CI_AS = 'Training Area'

      AND m.Maximo_Asset_number IS NOT NULL

      AND m.Region = '04'

      AND m.site_name = @param1

      AND MONTH(m.Voldate) = @param2

      AND YEAR(m.VolDate) = @param3

      AND m.Maximo_Asset_Number collate Latin1_General_CI_AS = t.GP22_AssetNumber

      AND m.region collate Latin1_General_CI_AS = t.RAR_RegionCode

GROUP BY m.site_name, t.GP22_AssetNumber, m.Maximo_Asset_number, m.VolDate

ORDER BY Voldate

How do i write this statement so that I can create unique objectid's for every row i need to insert. Is this possible or am I doing this completely the wrong way?

Hope someone can help!

Re: How to do bulk insert into Feature class from SQL - ObjectID's!!

AndrewCorcoran — Mon, 21 Nov 2011 08:01:21 GMT

>but obviously this only works for 1 row. My stored procedure could potentially be creating 800+ rows

Perhaps I've misunderstood your problem but I think this statement isn't correct. Each time you call the stored procedure to get the next OBJECTID for a feature class it updates the table storing the OBJECTIDs so that the next time you call it the OBJECTID will always be one higher. Modify your stored procedure to add (for SQL Server):

DECLARE @Object_ID int
DECLARE @Num_IDs int
EXEC dbo.i51_get_ids 2, 1, @Object_ID OUTPUT, @Num_IDs OUTPUT

where you'd replace the 'i51' with the correct value for your feature class from the SDE_table_registry, and then use the @Object_ID variable in your INSERT statement.

Hope this helps.

Re: How to do bulk insert into Feature class from SQL - ObjectID's!!

JoeyTuskan — Tue, 20 Jun 2017 09:53:33 GMT

You saved me so much trouble. Thank you.

Additionally, you can set the next OBJECTID to be inserted in the table "dbo.i51" with the column "last_id" (replacing i51 with the value from SDE_table_registry)