How to use advanced SQL statements to Bulk Insert in SQL table

Center of Excellence · February 3

1. Abstract

The purpose of this article is to demonstrate how advanced SQL statements can be used in the Bulk Insert process to enhance the integration from Board to SQL databases.

This article is not intended to be a comprehensive guide to all the functionalities, but rather to showcase three use cases that were tested and implemented in our project. The goal is to provide some insights and serve as a starting point, allowing you to adjust and expand upon them according to your specific needs.

Prerequisite for this article is that the reader already has knowledge and experience with "Bulk Insert to SQL Table" procedure step and basic knowledge of SQL statements.

2. Context

Starting with Board 12, the "Bulk Insert to SQL Table" procedure step was introduced to streamline the extraction of data from Board directly into SQL tables. This functionality is crucial as it enables the direct transfer of Board data into SQL databases, eliminating the need for intermediate flat files.

Since the Bulk Insert is a standard SQL command, it is possible to leverage common SQL statements to enhance the extraction process and simplify any necessary data transformations.

3. Content

This document presents three practical examples demonstrating the use of advanced SQL statements within the "Bulk Insert to SQL Table" feature. These examples showcase how we effectively utilized this functionality to address and fulfill specific integration requirements. By exploring these scenarios, readers can gain valuable insights into leveraging advanced SQL techniques to enhance data integration processes in a variety of contexts.

3.1 How to use a filter in Bulk Insert

The best practice for integration is to first push data from Board into staging SQL tables that are configured without constraints. Afterward, SQL procedures should be used to handle the transformation and validation of the exported data.

However, we encountered situations where the database administrators preferred non-nullable columns in the staging table to prevent the insertion of invalid rows.

Both the "Export Dataset" and "Bulk Insert to SQL Table" functions do not offer the filtering options available in the "Export Data View to File" feature. As a result, data validation filters cannot be applied during extraction, which could lead to invalid or incomplete data being inserted into the staging tables. Additionally, if there are non-nullable fields, this could cause the procedure to fail.

To address most of these cases, we prepared the data in temporary cubes. These temporary cubes, used during the extraction process, were populated only with valid data combinations. While this solution covered most cases, there were situations where managing the large number of combinations became cumbersome, making it challenging to filter and manage the data effectively.

To enhance the effectiveness of our extractions and reduce failures caused by invalid combinations (such as null values in non-nullable fields or invalid values in key fields), we opted to incorporate advanced SQL statements, utilizing WHERE conditions within the Bulk Insert statement.

The use of WHERE conditions requires to write the entire Insert statement in a different way.

Using the Advanced editor for bulk insert we have 4 field:

Header
Body
Separator
Footer

The standard usage is to compile it as following:

Header

INSERTINTO<db_schema>.<TargetTable>

(Column1, Column2)

VALUES

Body

(@Field1, @Field2 )

Separator

,

Footer

;

In order to use a WHERE condition in SQL statement we need to adjust our query to use the SQL INSERT INTO SELECT statement, which in looks like:

INSERT INTO table2 (column1, column2, column3,...)SELECT column1, column2, column3,...FROM table1WHERE condition;

For example, let’s assume we need to filter out all rows where our @Field2 = 0. Our SQL statements blocks will be like:

Header

INSERTINTO<db_schema>.<TargetTable>

(Column1, Column2)

Body

SELECT @Field1, @Field2 WHERE @Field2 <> 0

Separator

Union All

Footer

;

Main differences between this and the previous one are:

Insert Into clause doesn’t require the VALUES keyword
In the Body we need to use a SELECT clause, declaring the @Field as usual and adding the WHERE clause. In our case it was a MS SQL Server so the FROM clause was not needed, in case of other databases, like Oracle, you may need to specify the “FROM Dual”, so your Body will be like: SELECT @Field1, @Field2 FROM Dual WHERE @Field2 <> 0
Separator is now UNION ALL: we preferred to use UNION ALL instead of UNION for performance reasons and for the fact that we don’t need to check for duplicates (since the cube structures drive the extractions, we were sure that it was not the case).

After all the blocks are compiled, please ensure that the result of the query is correct. The system automatically concatenates all statements, so it can happen that it is not correctly formatting the instructions. One case is that in separator we had to write the “ UNION ALL “ so with a space in the beginning and one in the end.

Without the spaces, you will have a wrong result that will cause the failure of the SQL instruction:

“INSERT INTO <db_schema>.<TargetTable>

(Column1, Column2)SELECT @Field1, @Field2 WHERE @Field2 <> 0Union All

SELECT @Field1, @Field2 WHERE @Field2 <> 0Union All

SELECT @Field1, @Field2 WHERE @Field2 <> 0;”

Therefore we suggest to use the proper space and carriage return to make SQL statement easy to read.

3.2 How to convert Date into Datetime

In our project, we were asked to log the last execution of Board procedures in a text cube displayed on Board screens. This was accomplished by using a standard Text cube populated through a dataflow step, utilizing the common @Datetime substitution formula. As you may know, the format of the datetime value written in the text cube depends on the machine settings. In our case, the format followed was: dd/mm/yyyy hh:mm:ss.

As part of the requirements, we were asked to export this information into a SQL table. In our case, the target column in the SQL table was set to the Datetime type. However, since we were trying to insert the string into a column of type Datetime, we encountered an error because SQL could not accept the specific format we were using.

The solution was to apply a conversion directly within the Bulk Insert statement, as shown below:

INSERTINTO<db_schema>.<TargetTable>

(Column1, Column2)

VALUES

(@Field1@Field1 ,

CONVERT(datetime, @Field2, 103)

);

@Field1 is our “procedure” and @Field2 the “execution log”. In this example, the CONVERT() function was used to explicitly convert the string into a valid Datetime format that SQL could accept. The 103 style code was chosen to match the dd/mm/yyyy format of the string.

3.3 How to handle NULL values in Bulk Insert

It is well known that Board does not differentiate between 0 and NULL, nor between an empty string ("") and NULL in text cubes. In our project, we enabled the "Differentiate Zero and NULL" function in our layouts, which applies to numerical cubes. However, this function is not needed for text cubes, since an empty string is considered NULL by definition.

During the integration from Board to SQL, we encountered an issue: in the execution of the Bulk Insert, Board would write a 0 for empty numerical values and an empty string ("") for empty cells in text cubes. This behavior was not acceptable for our use case, as we needed to differentiate between 0 and NULL for numerical values and handle NULL values correctly for text fields in the SQL tables.

To address this, we implemented additional logic in our Bulk Insert process to explicitly convert empty values into NULL for both numerical and text fields. This was achieved using a CASE WHEN function in the SQL query, as shown below:

INSERTINTO<db_schema>.<TargetTable>

(Column1, Column2, Column3, Column4, Column5)

VALUES

(@Field1@Field1 ,

CASEWHEN @Field2 = 0 THENNULLELSE @Field2 END,

@Field3 ,

CASEWHEN @Field4 =''THENNULLELSE @Field4 END,

@Field5 );

In this example:

For numerical columns (Columns2 - @Field2), we check if the value is 0 and convert it to NULL. If the value is not 0, we leave it unchanged.
For text columns (Column4 - @Field4), we check if the value is an empty string ("") and convert it to NULL. If the value is not empty, we retain it as is.

This ensures that empty or zero values are correctly handled and inserted as NULL in the SQL database, maintaining the intended differentiation between 0 and NULL for numerical fields and NULL for text fields.

4. Conclusion

"Bulk Insert to SQL Table" is an incredibly useful feature in Board that significantly enhances the integration between Board and external databases. By leveraging advanced SQL statements, this functionality strengthens the integration process by enabling a layer of data transformation and control directly at the extraction stage.

This approach not only streamlines data handling but also empowers users to address complex integration requirements with greater precision and efficiency. As previously mentioned, this guide is not intended to be comprehensive but rather serves as a practical example to demonstrate how to construct SQL statements for use within the "Bulk Insert to SQL Table" feature.

By combining expertise in both Board and SQL, users can tailor their queries to meet specific integration needs and tackle challenges effectively, making the integration process more robust and adaptable to varied scenarios.