Skip to main content
Solved

IDENTITY_INSERT for several tables

  • September 7, 2015
  • 3 replies
  • 57 views

Hi guys,

 

 

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

Hi,

 

 

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
View original
Did this help you find an answer to your question?

3 replies

takashi
Influencer
  • Best Answer
  • September 8, 2015
Hi,

 

 

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

ascollins1
Contributor
  • Contributor
  • January 23, 2023

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!


evieatsafe
Safer
  • Safer
  • January 23, 2023
ascollins1 wrote:

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​ 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings