The following structure can be improved using the ternary operator
<cfloop query="local.Q_Data>
<cfif QueryKeyExists(local.Q_Data,"FILE_OPERATOR")>
</cfif>
</cfloop>
There is yet another place where the code can be improved. The code invokes a component to check for a null value: this.comp('Imports').checkIsNull(FILE_OPERATOR). This is not only expensive, it is also unnecessary.
At this point in the code we have local.Q_data. Therefore we know which values of local.Q_Data['FILE_OPERATOR'] are null or the empty string. (Note: in ColdFusion, null may be stored as empty-string within a query-result. )
So, let's put the two suggestions together. To do so, I shall assume that what you want to achieve is similar to the following:
INSERT INTO MyTable
( Column1, Column2, fileOperator, column4, column5)
VALUES
(1, 103, 'Operator One', 'Mumbai', 'India'),
(2, 105, 'Operator Two', 'Beijing', 'China'),
(3, 107, 'Operator Three', 'Paris', 'France'),
(4, 109, 'Operator Four', 'London', 'UK'),
(5, 111, 'Operator Five', 'New York', 'USA'),
(6, 113, 'Operator Six', 'Milan', 'Italy'),
(7, 115, 'Operator Seven', 'Rio de Janeiro', 'Brazil'),
(8, 117, 'Operator Eight', 'Johannesburg', 'South Africa')
That is to say, I am assuming that you intend to use a loop to insert each row of values. Then the required code will be something like:
<cfquery >
INSERT INTO MyTable (
Column1,
Column2,
fileOperator,
Column4,
Column5)
VALUES
<cfloop query="local.Q_Data">
<cfset currentFileOperatorValue=queryKeyExists(local.Q_Data,"FILE_OPERATOR") ? "#FILE_OPERATOR#" : "">
(
#Column1#,
#Column2#,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(currentFileOperatorValue)#" null="#trim(currentFileOperatorValue) eq ''#">,
'#Column4#',
'#Column5#')
<cfif CURRENTROW LT RECORDCOUNT>
,
</cfif>
</cfloop>
</cfquery>
Some explanations:
value="#trim(currentFileOperatorValue)#" null="#trim(currentFileOperatorValue) eq ''#" means that null will be inserted into the database if trim(currentFileOperatorValue) eq '' is true. Otherwise, trim(currentFileOperatorValue) will be inserted instead.
<cfif CURRENTROW LT RECORDCOUNT> ,</cfif> ensures that a comma is added between the rows that are to be inserted.
That's it.
You should also have a look at the INSERT INTO SELECT statement.
... View more