Dynamic data import / export

datenbank

simply more productive
Up to 10,000 records can be processed with the freeware version!

Adapter SQL Server

The MS SQL Server Adapter is used to import (insert), export (select) and amend (update) data in tables of a Microsoft SQL Database Server. This Adapter can be used on the READ (export) side as well as on the WRITE (import) side and supports SQLServer versions from SQL Server 7.0 up to the latest SQL Server 2016.

 

MS SQL Server Adapter, database select
MS SQL Server Adapter, database select

General tab

Connection and authentication

Server Name or IP: You specifiy here which SQL Server that FlowHeater should connect to and what authentication mechanism should be used. Either a DNS host name or an IP address can be entered here. If applicable, an instance name (from SQL Server Version 2000) can be specified here in addition, using the notation:

Server-name/Instance-name

Database: Choose here the name of the SQL Server database that is to be accessed. On clicking the Test connection button the dropdown selection is automatically filled out with the names of databases available on the SQL Server specified.

Authentication: Select either Windows (trusted connection) or SQL Server here. This confirms how FlowHeater should login to the SQL Server database.

trusted connection = means FlowHeater logs on to the SQL Server database using the currently logged in Windows user.
SQL Server = means FlowHeater logs on to the SQL Server database with a specific SQL user name and password.

User / Password: If you selected "SQL Server" for the Authentication option, you must enter here the SQL user name and its associated password. Important: The password is only stored for future use if you check the "Save Password" option. All passwords stored are saved in an encrypted form in the Definition file.



Common

Insert data: When this option is checked, SQL Insert statements are generated.

Ignore existing records: During an import and when this option is checked, records that already exist in the table are ignored.

Empty the table before importing: When this is checked you tell the SqlServer Adapter to empty the contents of the table prior to running the import, effectively deleting all existing rows.

Update data: When this option is checked, SQL Update statements are generated. Note: If both the Insert and Update options are checked, the SqlServer Adapter checks whether an SQL Update or Insert should be generated in each instance, by reference to the PrimaryKey. Tip: If you are certain there is only data to insert then avoid checking the Update option, as this will make the process significantly faster.

No updates or inserts to data, only generate an SQL script: When this option is checked, it signals the SqlServer Adapter to make no inline changes to the database, but instead to store an SQL script with Insert and/or Update statements. This is useful for testing during development and for subsequent application to the database. If this option is checked you should also check the option below and enter the filename that the SQL statements are to be stored in.

Generate SQL script: This option instructs the SqlServer Adapter to store the change statements (Insert, Update) as an SQL script file with the specified name and path.

 

MS SQL Server Adapter, fields and data types
MS SQL Server Adapter, fields and data types

Fields / Data types tab

SQL: data available varies according to the side of the Adapter in use:

On the READ side: here you can choose from Tables, Views and Procedures.
On the WRITE side: only Tables are available.

On the READ side you also have the possibility to enter complex SQL statements in the text field. Table joins must be defined by hand. In the second combo box the tables, views and procedures are listed that are available in the specified SQL Server database.

Fields: When you click the Load Schema button, information is retrieved from the database schema (field names, field sizes, data types, primary key, etc.) for the SQL statement above. The information about the fields is then loaded into the field list to the left of this button.
Note: The fields in the field list can be ordered in any sequence required. Fields that are not required can either be temporarily disabled here (tick removed) or simply deleted.

Field properties: How the properties of Primary Key and Auto Increment for the currently highlighted field are to be interpreted are adjusted. This information is only required on the WRITE side. No changes are needed here generally, since the correct information is usually obtained directly from the schema.

A PrimaryKey field is used in an Update to identify record that possibly exists.
Auto Increment fields are neither assigned nor amended in Insert/Update statements.

Warning: If you make changes here it can result in more than one record being updated with an Update!

 



Adapter settings in the Format tab

The use of the Format tab is described in detail under general information about the usage of Adapters



Examples

 

Microsoft®, Windows®, SQL Server® are registered trademarks of Microsoft Corporation