How to create autoincrementing primary key with dynamic writer in FME

Question

I'm writing 3 shapefiles to an MSSQL Spatial database with FME. I'm using a writer with dynamic settings to simplify future updates of the input files.

In order to create a spatial index with the dynamic writer I need a primary key column. Right now, I'm accomplishing this with a Counter that writes to a manually created "pid"-attribute marked as primary key, but this feels like a hack.

So, how do I create an auto-incrementing primary key on a dynamic database writer in FME?

Answer 1

No experience with MSSQL but for PostgreSQL it works by selecting the datatype serial and the index PrimaryKey. See screenshot.

I suspect the datatype should be identity with index PrimaryKey. See also this documentation.

Big StackExchange fan but you will have better chances getting a good answer on the Safe Software forum. Lots of knowledge and activity.

Answer 2

I think @buddiebubba is correct. Here's what my SQL Server definition looks like:

Note that it's a dynamic translation. The Feature Type also has a parameter like this:

By setting that to NO I cause SQL Server to increment that attribute, which it does nicely. However, I do get a warning message in the FME log:

Microsoft SQL Server Spatial Writer: 
Data type `identity' of attribute `NewMarkID' of feature type `Parks' 
is not supported for writing.  Attribute will be ignored by writer

I think that must be just a bad warning (or just badly worded). I don't see that the attribute is ignored. It all looks good to me.

Now, if I want to read from a SQL table and write the data back to it, then I would set the above parameter to YES. Then the existing values are written back. Extra features can be given an ID with the Counter and they will get incremented from the previous max ID I believe.

NB: I've filed a request to update the warning message, so that it is not so confusing. It's PR#81236

Answer 3

Edit: answer by buddiebubba is better.

If using a dynamic writer type, it is not possible to simply put serial in there since the schema is feature driven.

The easiest way to do this, I found was to use the SQL To Run After Write feature:

For postgresql the code would be:

ALTER TABLE testing
ADD COLUMN pkid serial;
alter table testing add CONSTRAINT pk_testing PRIMARY KEY (pkid);

I am sure there is an equivalent for SQL server. It would be something like:

ALTER TABLE Parent MODIFY Identifier smallint(10) AUTO_INCREMENT;

if the internet is to be trusted.