Description
This covers how to use Qlik to insert data from a Qlik table into a SQL database using script generation. This is done for all tables within a Qlik model where the Qlik and Database table and field names are the same.
*There is no data type validation/manipulation aside from making blanks NULLs
Introduction
Before I get into the code, there are a few things that I want to cover.
INSERT
We will be inserting the data from Qlik into the database table using traditional SQL. If you are not familiar with SQL, it is quite simple. First you define the table and columns you are going to insert into and then the values you want to insert. data are mapped to the columns by listed order.
Syntax:
INSERT INTO table_name (column_name_1, column_name_2,... column_name_n) VALUES (%column_name_1_value%, %column_name_2_value%,... %column_name_n_value%);
If you want to upload more than one row, then you just need to add additional VALUE parameters within parenthesis and separated by a comma.
INSERT INTO table_name (column_name_1, column_name_2,... column_name_n) VALUES (%column_name_1_value%, %column_name_2_value%,... %column_name_n_value%), (%column_name_1_value%, %column_name_2_value%,... %column_name_n_value%), (%column_name_1_value%, %column_name_2_value%,... %column_name_n_value%);
For more info take a look at this tutorial.
One big difference between Qlik and SQL is that data types are strictly enforced. If you try to insert a string value into a field that is an int (integer) field, you will get an error. This will be touched on later.
!EXECUTE_NON_SELECT_QUERY
When sending a query in Qlik to a database, Qlik expects a response. Ideally some data but sometimes and error. However, when we are writing data to the database, Qlik doesn’t receive the response it expects. To overcome this we have to do two things:
1. Enable allow-nonselect-queries in file C:\Program Files\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage\QvOdbcConnectorPackage.exe.config by setting the parameter to True.
2. Append your query with !EXECTUTE_NON_SELECT_QUERY
Put this before the semi-colon of the query.
Qlik Example:
SQL INSERT INTO QlikLog (ID, App_GUID, Comment) VALUES (1, 'e6b9511f-bc25-4e65-ab7c-25bf0f5c8a93', 'Testing SQL Insert') (2, 'e6b9511f-bc25-4e65-ab7c-25bf0f5c8a93', 'Testing Multiple Rows') !EXECUTE_NON_SELECT_QUERY;
Subroutine
So given we need to format the Qlik data into a SQL statement, we will need to do some prep work. This basically entails combining the data from multiple columns into a single column within some additional formatting.
I have made the following subroutine to do this:
ConvertToSQLValues
Note:
This subroutine does not account for any data type validation. However, one data cleansing thing it does is replace blanks with NULLs. This is so that when there is an empty value in a numeric field, we don’t pass an empty ” literal and fail the validation
check. So if you have a value that is supposed to be blank in Qlik and not NULL, this will be changed to NULL.
Code
Sub ConvertToSQLValues(pTable,pBatchSize)
For ctsv.f=0 to NoOfFields('$(pTable)')
[ctsv.Field_tmp]:
Load
FieldName($(ctsv.f),'$(pTable)') as [ctsv.FieldName]
AutoGenerate
(1);
Next ctsv.f;
[ctsv.ConcatScript]:
Load
'SQL INSERT INTO $(pTable) (' & Concat([ctsv.FieldName],','&chr(10)) & ')' as [ctsv.InsertClause],
'Chr(39) & ' & Concat([ctsv.FieldName],' & chr(39) & Chr(44) & chr(39) & ') & ' & Chr(39)' as
[ctsv.ValueConcat]
Resident
ctsv.Field_tmp;
Let ctsv.vInsertClause = Peek('ctsv.InsertClause', 0, 'ctsv.ConcatScript');
Let ctsv.vValueConcat = Peek('ctsv.ValueConcat', 0, 'ctsv.ConcatScript');
Drop Tables [ctsv.Field_tmp],[ctsv.ConcatScript];
[$(pTable).SQLValues]:
Load
'$(ctsv.vInsertClause)' as [$(pTable).Insert],
[$(pTable).BatchId] as [$(pTable).BatchId],
Concat([$(pTable).Values],','&Chr(10)) as [$(pTable).Values],
Group by
[$(pTable).BatchId];
Load
Ceil((RecNo()/$(pBatchSize))) as [$(pTable).BatchId],
Replace('('&$(ctsv.vValueConcat)&')', Chr(39)&Chr(39), 'NULL') as [$(pTable).Values],
Resident
[$(pTable)];
ctsv.f=;ctsv.vInsertClause=;ctsv.vValueConcat=;
End Sub;
Example
Output Table
Code
Test:
Load
'ID-'&RowNo() as ID,
Round(Rand(),0.00) as Random,
'Two' as Two,
Null() as Null
AutoGenerate
(10);
Sub ConvertToSQLValues(pTable,pBatchSize)
For ctsv.f=0 to NoOfFields('$(pTable)')
[ctsv.Field_tmp]:
Load
FieldName($(ctsv.f),'$(pTable)') as [ctsv.FieldName]
AutoGenerate
(1);
Next ctsv.f;
[ctsv.ConcatScript]:
Load
'SQL INSERT INTO $(pTable) (' & Concat([ctsv.FieldName],',') & ')' as [ctsv.InsertClause],
'Chr(39) & ' & Concat([ctsv.FieldName],' & chr(39) & Chr(44) & chr(39) & ') & ' & Chr(39)' as
[ctsv.ValueConcat]
Resident
ctsv.Field_tmp;
Let ctsv.vInsertClause = Peek('ctsv.InsertClause', 0, 'ctsv.ConcatScript');
Let ctsv.vValueConcat = Peek('ctsv.ValueConcat', 0, 'ctsv.ConcatScript');
Drop Tables [ctsv.Field_tmp],[ctsv.ConcatScript];
[$(pTable).SQLValues]:
Load
'$(ctsv.vInsertClause)' as [$(pTable).Insert],
[$(pTable).BatchId] as [$(pTable).BatchId],
Concat([$(pTable).Values],','&Chr(10)) as [$(pTable).Values]
Group by
[$(pTable).BatchId];
Load
Ceil((RecNo()/$(pBatchSize))) as [$(pTable).BatchId],
Replace('Values('&$(ctsv.vValueConcat)&')', Chr(39)&Chr(39), 'NULL') as [$(pTable).Values]
Resident
[$(pTable)];
ctsv.f=;ctsv.vInsertClause=;ctsv.vValueConcat=;
End Sub;
Call ConvertToSQLValues('Test',3);
SQL
Once we have a method to format the data as we need it, all that’s left is for us to execute the SQL in the Qlik load script.
This can be done using a loop that does the following:
1. Get Table Name
2. CovertToSQLValues
3. Generate Qlik SQL statement and store in variable
4. Execute Qlik SQL statement variable using dollar sign expansion
5. Repeat
Code
Let vDBConnection = '%Database Connection Name%';
Let vLB = chr(10); // Line Break
For t = 0 to NoOfTables() - 1
Let vTable = TableName($(t));
Call ConvertToSQLValues('$(vTable)',500);
For i = 0 to NoOfRows('$(vTable).SQLValues')-1
Let vSQL = 'Lib Connect To '& chr(39) & '$(vDBConnection)' &chr(39) &'; $(vLB)$(vLB)' &
Peek('$(vTable).Insert',$(i),'$(vTable).SQLValues') & '$(vLB)' &
'Values $(vLB)' & Peek('$(vTable).Values',$(i),'$(vTable).SQLValues') &
'$(vLB)$(vLB)!EXECUTE_NON_SELECT_QUERY;$(vLB)$(vLB)Disconnect;$(vLB)$(vLB)';
$(vSQL)
Drop Tables [$(vTable).SQLValues];
vSQL=;
Next i;
i=;
Next t;
t=;
Code Generation
The only thing worth mentioning in the loop is the code generation piece. What we are doing is creating a script statement using data in the return table created by ConvertToSQLValues.
So vSQL ends up being something like this:
Lib Connect To 'QVD Catalog DB';SQL
INSERT INTO QlikLog (ID, App_GUID, Comment)
VALUES
(1, 'e6b9511f-bc25-4e65-ab7c-25bf0f5c8a93', 'Testing SQL Insert')
(2, 'e6b9511f-bc25-4e65-ab7c-25bf0f5c8a93', 'Testing Multiple Rows')
!EXECUTE_NON_SELECT_QUERY;DisConnect;
Note:
We are connecting and disconnecting on each SQL call. This isn’t mandatory, but done because I have seen issues when executing many calls against a single DB connection opening. This may take longer, but should alleviate weird occurrences.
