SQL Server Import IDENTITY (Auto Increment) fields
This example demonstrates how to import into Auto Increment (IDENTITY) fields into an MS SQL Server database using the FlowHeater SQLServer Adapter and what to watch out for when importing an Auto Value (IDENTITY) column.
This example builds upon the "CSV text file import into SQL Server" example. You can reuse its MS SQL Server table and CSV text file for importing.
Note: Auto Increment (IDENTITY) insert only functions with FlowHeater version 3.3.0 and above
Let’s get started
First create a new FlowHeater Definition and select for the READ side the TextFile Adapter in order to process a CSV text file as input. On the WRITE side use the SQLServer Adapter and connect to the MS SQL Server and database that contain the example table.
Enabling Auto Value insertion
SQL Server - Insert IDENTITYOpen the Configurator of the SQLServer Adapter on the WRITE side and configure the general parameters such as server and database name. Next carry out a connect test to verify whether it is possible to establish a connection to the SQL Server with the specified values. Now switch to the “Fields / Data types” tab, select the table “Import” and click the “Read Schema” button. The fields names, sizes, primary key information and whether a field of Auto Increment (IDENTITY) type exists in the table will be loaded. In our example, the “Import” table has an identity field “ID” and this column also the primary key. Select from the list of fields “ID” and uncheck its property “Auto Increment” (see screenshot). That is absolutely necessary, because FlowHeater normally omits an “Auto Increment” column when it generates an SQL Insert statement.
SQL Server - Enable IDENTITY insertsThe next step is to prepare the database and table. The preparation necessary is done for you by FlowHeater: switch to the “Advanced ” tab and checking the option “Permit Auto Increment (IDENTITY) Inserts.”
That’s all on the SQLServer Adapter WRITE side.
Synopsis:
In order to insert values into identity columns the following two steps are needed.
- Uncheck the FlowHeater property “Auto Increment” for the Auto Value column.
- In the “Advanced” tab, check the “Permit Auto Increment (IDENTITY) Inserts” option.
Configuring the CSV import
Here we make use of the same CSV text file “import.csv” from our earlier example. Open the Configurator for the TextFile Adapter on the READ side, select the file “import.csv” and then close the configuration popup window. In order to demonstrate that values are clearly inserted into the IDENTITY column and not simply from the ID values in the CSV file, we add an AutoID Heater and specify for its initial value something like 10,000. Connect the output of the AutoID Heater to the “ID” field of the SQL Server. Connect all other fields as described in the “CSV text file import into SQL Server” example.
Now run the Definition and take a look at the result. Try to repeat the import with this Definition and you will receive an error message like “Violation of PRIMARY KEY constraints. A duplicate value cannot be assigned…“
To avoid encountering this error message you can either reconfigure FlowHeater to perform updates rather than inserts, effectively replacing the existing data, or you can check “Ignore existing records” in the General tab “Common” options so that repeat run results in no changes to the output file.
Important: When values are inserted in the way described into Auto Value (IDENTITY) columns and an inserted value is higher than the last automatically incremented value assigned by the database, then the next value the database will automatically assign is revised to this value (+1).
