Dynamic data import / export

datenbank

simply more productive
Up to 10,000 records can be processed with the freeware version!
Welcome, Guest
Username: Password: Remember me
Welcome to the FlowHeater support forum.

Questions about using FlowHeater are answered here.

TOPIC: SQL Server - set identity_insert on

SQL Server - set identity_insert on 5 years 10 months ago #2087

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 239
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.
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.
The administrator has disabled public write access.

Re:SQL Server - set identity_insert on 5 years 10 months ago #2088

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 239
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.
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.
The administrator has disabled public write access.
Moderators: FlowHeater-Team
Time to create page: 0.071 seconds