I tried using IDENTITY_INSERT on several tables of single SQL Server Non-Spatial writer. There is a error I'm getting that says I cann't do that. I now for the rule that SET IDENTITY INSERT can only be used on a single table per connectio, but I wonder if there is some way to make my 4 tables use this feature with a single writer? These tables aren't beeing populated at same time, so can I do something with SQLExecutor for example or no?
Best answer by takashi
As you mentioned, IDENTITY_INSERT can only be used for a single table per connection. It's a limitation on the Transact-SQL specification.
I think the SQLExecutor would be a workaround. e.g.
-----
FME_SQL_DELIMITER ;
SET IDENTITY_INSERT @Value(table_name) ON;
@Value(insert_statements);
SET IDENTITY_INSERT @Value(table_name) OFF;
-----
Here, assume that "insert_statements" stores SQL INSERT statements delimited by semi-colon. I think you can create its value with some transformers, e.g. StringConcatenator, Aggregator etc.
Takashi
As you mentioned, IDENTITY_INSERT can only be used for a single table per connection. It's a limitation on the Transact-SQL specification.
I think the SQLExecutor would be a workaround. e.g.
-----
FME_SQL_DELIMITER ;
SET IDENTITY_INSERT @Value(table_name) ON;
@Value(insert_statements);
SET IDENTITY_INSERT @Value(table_name) OFF;
-----
Here, assume that "insert_statements" stores SQL INSERT statements delimited by semi-colon. I think you can create its value with some transformers, e.g. StringConcatenator, Aggregator etc.
Takashi
I think a better workaround is to add each table as a separate writer, rather than constructing insert statements for a SQLExecuter. Looks like both writers can still use the same connection in the same workbench but not attempt IDENTITY_INSERT on two tables in the same writer. The error message is misleading.
It would be great if FME handled this case and we didn't have to use this workaround though please!
I think a better workaround is to add each table as a separate writer, rather than constructing insert statements for a SQLExecuter. Looks like both writers can still use the same connection in the same workbench but not attempt IDENTITY_INSERT on two tables in the same writer. The error message is misleading.
It would be great if FME handled this case and we didn't have to use this workaround though please!
Posted an idea, go vote on it for this to be included in future versions! @Andrew Collins @aleksandar
