Skip to main content
simply more productive

The Data Specialist

Thanks to our intuitive transformation designer, you can convert data effortlessly to a variety of formats without knowing how to program. It’s not only efficient, it’s also easy to use.
Flowheater Download

SQL Server - set identity_insert on

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
13 years 2 months ago #2087 by FlowHeater-Team
SQL Server - set identity_insert on was created by FlowHeater-Team
translated submission in the german forum. you can read the original entry here .

hello,

i am trying to migrate an sql database. it is important for me to be able to define a unique key myself, in order that i can associate data in another table using this key. however, a direct import always reports the error message: "an explicit value... cannot be inserted when identity_insert is set to off."

if i generate an sql script and edit it to manually edit it to insert the command "set identity_insert veis_invoice on" prior to the import statements, everything works properly.
Code:
use [test190] set dateformat 'ymd' begin tran set identity_insert veis_invoice on insert into [veis_invoice] ([pk_veis_invoice], [entitydate], [entitystate], [reqnumber], [companyid], [personalaccountid], [inv_type], [invoicetype], [amountbasecurrency], [currencyid], [referencenumber], [documentdate], [transactiondate], [modeofpayment], [factoring]) values(1500171601151820, '2010-12-28 13:02', 0, 15001716, '115', 1151817, 'tte_customerinvoice', 'ite_invoice', 719.71, 'eur', '00015001716', '2010-09-08', '2010-09-08', 'pte_default', 'fte_no') commit tran

is there some way to specify this command should be executed first, to enable me to write directly to the database?

thanks
martin

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in to join the conversation.

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
13 years 2 months ago #2088 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:SQL Server - set identity_insert on
Hallo,

That is an interesting requirement. You can achieve this with Version 1.3.0 and above, together with a .NET Script Heater and the following short C# Script.
Code:
bool bFirst = true; public object DoWork() { // only execute first time if (!AdapterWrite.OnlyTest && bFirst) { bFirst = false; // modify here the SQL table name AdapterWrite.Execute("set identity_insert [your_table] on"); } // just returning the first input parameter return InValues[0].GetValue(); }

Procedure: Drag and drop a .NET Script Heater onto any existing connection (pipe), the best choice is to use the field for Auto Increment (Identity). Next copy the script above (modified with your own table name – see comment in the script). Now open the Configurator on the WRITE side and in the SQL Identity field uncheck the property "Auto Increment". This simply tells FlowHeater to pass on the field to SQL Server, it does not modify the actual SQL Schema of the SQL Server table.

The .NET script gets executed once, and effectively generates the following command before the actual SQL Insert statements on the WRITE side (=SQL Server Adapter):

set identity_insert [YOUR TABLE] on

Thereafter AutoID (Identity) values per Insert can also be inserted on the specified SQL Server table.
Please note: The above only works in combination with the SQL Server Adapter .

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in to join the conversation.

Time to create page: 0.259 seconds

FlowHeater - The Data Specialist

Efficient data integration and transformation with FlowHeater – the perfect solution for a seamless transfer of your data.

Rechtliches

Support & Contact

Kontaktinformation

Telefon:0951 / 99339792 E-Mail:This email address is being protected from spambots. You need JavaScript enabled to view it.

Copyright © 2009-2024 by FlowHeater GmbH. All rights reserved.