topic Re: Bulk insert into MS SQL Creating ObjectID's in Data Management Questions

Bulk insert into MS SQL Creating ObjectID's

DaveWatson — Mon, 21 Apr 2014 19:34:26 GMT

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,

Re: Bulk insert into MS SQL Creating ObjectID's

DaveWatson — Tue, 22 Apr 2014 15:10:43 GMT

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