Bulk insert into MS SQL Creating ObjectID's

1895
1
04-21-2014 12:34 PM
Highlighted
New Contributor II
I am running an ETL from a spatial view into publication database. The view has results in duplicate ObjectID's. I started working with the get_ids SP example from ESRI, but that seems to be built more for a single record insert and not a bulk insert. I have also tried the NEXT_ROWID. Here is my example...

DECLARE @id as integer
DECLARE @num_ids as integer
EXEC L.i16_get_ids 2, 1, @id output, @num_ids output;

INSERT INTO L.TAXPARCELWCONDOS ([OBJECTID]
           ,[PARCELNO]
           ,[SHAPE])
SELECT @id
      ,[PARCELNO] ,[shape]
  FROM [Parcel]..[view_TaxParcelwCondos];
END
How can I generate new ObjectID's for the insert.

Thanks,
Reply
0 Kudos
1 Reply
Highlighted
New Contributor II
The solution that I came up with...
Create a temp table with an identity column for Object ID.
Insert the View there and then move that into the publication table.

CREATE TABLE #TAXPARCELWCONDOS(
[OBJECTID] int identity(1,1) not null,
[PARCELNO] [nvarchar](26) NULL,
[SHAPE] [geometry] NULL)



INSERT INTO #TAXPARCELWCONDOS ([PARCELNO]
           ,[SHAPE])
SELECT [PARCELNO] ,[shape]
  FROM [Parcel]..[view_TaxParcelwCondos];



TRUNCATE TABLE L.TAXPARCELWCONDOS

INSERT INTO L.TAXPARCELWCONDOS ([OBJECTID],[PARCELNO]
           ,[SHAPE])
SELECT [OBJECTID],[PARCELNO] ,[shape]
  FROM #TAXPARCELWCONDOS;
 
DROP TABLE #TAXPARCELWCONDOS