Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • Data
  • Is there someway to automatically add an IDENTITY column when writing to SQL Server spatial?

Is there someway to automatically add an IDENTITY column when writing to SQL Server spatial?

I've found MapInfo data inserted into SQL Server spatial is flagged as invalid when I try to load it in QGIS. The solution seems to be to add an integer "id" column, which I set as an IDENTITY

I can manually add the ID column after a first run of FME has created table plus columns. Then truncate the SQL table and run the workspace a second time to populate with the actual data and the ID column set by SQL Server.

But I've got a lot of MapInfo layers to migrate, so is there a better way to do this in FME?

I haven't tried this but could you 1. add a counter to create the ID column, 2. use a FeatureWriter to write to SQL and then 3. use a SQLExecutor to change the SQL datatype afterwards?

Can you run it as a dynamic translation and add that column into the feature type definition? That way you should get all the original attributes, plus every table will get the new id column.

You should be able to populate the identity column the FME SQL Server writer. On the SQL Server writer feature type, under the Format Properties tab, Advanced disclosure panel you have the option to insert into identity column . If you leave the default setting then SQL Server will automatically populate the identity column for you.

To create the identity column, use the User Attribute tab and set the field type to be identity

Reply


Community Stats

30,102
Posts
114,269
Replies
37,736
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.