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

Question

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!

JoeyTuskan · Answer

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)

AndrewCorcoran · Answer

>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.