This article focuses on how you'd like to configure your import records. It excludes the general and advanced settings that are common across database connector forms.
Set up a connection to Microsoft SQL.
You can use standard SQL queries to modify as required, and import data into Micrsoft SQL. However, you can't use multiple SQL queries on a single flow step. If your flow requires multiple SQL queries, you must create one flow step for each query.
To import data into Micrsoft SQL, choose an import type based on your requirements. Each import type is explained in the sections below.
Tip
You can ask Celigo AI () to generate an SQL query. Enter a description of your query in plain English in the space provided, and Celigo Ora will attempt to generate a query based on your prompt.
Note
This option is being introduced via a phased rollout and will soon be available to all users.
This import type is ideal for large data volumes as it aggregates data before loading, reducing the need for numerous batch inserts and significantly improving performance. It chunks the large data volumes into smaller fixed-size files. You can load all the files together (default), or load them as they are created using the Load data as available checkbox.
Additionally, integrator.io automatically generates insert or merge queries based on the destination table and primary keys, allowing you to update existing data seamlessly without the need for a separate deduplication flow.
Destination table: Select the destination table into which the data must be inserted in either of these ways:
-
By validated table name: Begin typing the table name in the Destination table field, and any table that already exists in your database environment will display in a list filtered by the text you enter.
-
By referencing a dynamic lookup with a handlebars expression that identifies the destination table: Use a handlebars expression to reference the lookup using the following format:
{{lookup.<lookup name>}} Ex: {{lookup.GoogleBigQueryTableLookup}}
Primary keys: Select the primary keys so that your data can be matched based on these fields and then merged in the destination table. If your table already has primary keys, they will be automatically shown in this setting. You can select one or more fields to designate as primary keys. These fields uniquely identify each record in your table. (After you create your import, map these values (fields) that you've provided in your Primary keys setting.)
Mapping -related
When configuring field mappings for your SQL Server bulk load import, follow these guidelines
Auto-managed columns: Don’t map auto-managed columns because MS SQL automatically generates or calculates values in these columns. For instance,
-
IDENTITY
-
COMPUTED
-
ROWVERSION / TIMESTAMP
-
Temporal / ledger period columns
-
Always Encrypted columns
If you map them, then it will result in mapping errors causing the import to fail.
Mapping NOT NULL columns (with and without defaults)
-
NOT NULL column with a default value: Leave it unmapped. When a column is unmapped, SQL Server applies its default value. If you map the column and a record has no value for it, the platform writesNULL, which violates theNOT NULLc onstraint and causes the import to fail.
-
NOT NULL column without a default value: Map it. These columns must receive a value from your source data on every record; if left unmapped, the import fails because there’s nothing to satisfy the NOT NULL constraint.
-
If you leave Primary keys blank, it creates an insert query. Insert without primary keys may lead to duplication.
-
If you select values in Primary keys, it creates a merge query.
Override merge/insert statement: If you check this box, you can override the existing merge/insert statment by entering custom load logic using Handlebars in the SQL box or Advanced Field Editor (AFE).
Load data as available: If you check this box, it loads files as they are created. It skips failed files, and the records are flagged as errors. If you don't check this box, then it will be loaded in one-go when all the files are ready.
Note
The bulk load data option is only available for cloud-based Microsoft SQL imports.
The batch insert data option is helpful for moderate data volumes. integrator.io builds the insert query for you automatically for each batch of records. Each batch's default number of records bound is 100, but you can use the Batch size field in the Advanced section to tune your imports as needed. There is a query limit of 100KB, so for larger batch sizes, the number of records imported in a single request is adjusted to the size limit.
Note
For bulk insert, if you have data types such as money, numeric, decimal, smalldatetime, then you must check if the values to be inserted are within the accepted range of the destination column. If the values are out of range, use another data type that supports the required range. To verify the range based on the server version used, see Data types (Transact-SQL).
In the Destination table:, using metadata, you can:
-
Search for a specific database, dataset, table, or column to add to your query.
-
Use lookups to find data.
-
Refresh your data to get new updates automatically.
You can also use Mapper 2.0 to map imported records.
Note
The batch insert data option is only available for cloud-based Microsoft SQL imports.
Execute a SQL query once per page of data. You can write your SQL command in the SQL query box. Click the double curly braces next to the box to open the SQL Query builder AFE.