troubleshooting Question

Inserting data from one table to another with NULLS in SQL Server / Coldfusion

Avatar of diecasthft01
diecasthft01 asked on
ColdFusion LanguageSQLMicrosoft SQL Server
9 Comments1 Solution23 ViewsLast Modified:
Good morning. I am hoping to get some assistance with an SQL / CF query. I have a CF app that part of it queries an existing table in SQL Server and then inserts that data into another empty SQL Server table. This worked fine in Oracle but in my migration to SQL it fails. The problem seems to be that the insert fails at a specific field (EXPENSES) becasue that field has NULLS in it. My SQL "EXPENSES" field is a numeric field (18,2). My code is below:
 
First I grab the data from the existing table:
<cfquery name="ListERP19" datasource="SQL_MCA">
SELECT * FROM MCA.ERP_CUR
</cfquery>
 
Then I insert it into the new table.
<CFLOOP query="ListERP19">
<cfquery name="COPYNEWcsv" datasource="SQL_MCA">
         INSERT INTO MCA.ERP_PREV(ALLOTMENT, COMMITMENT, OBLIGATION, EXPENSES, WBS_ALL)
         VALUES
                  ( '#ListERP19.ALLOTMENT#',
                   '#ListERP19.COMMITMENT#',
                   '#ListERP19.OBLIGATION#',
                   <cfqueryparam value="#ListERP19.EXPENSES#" scale="2" cfsqltype="CF_SQL_NUMERIC" null="no">,
                  '#ListERP19.WBS_ALL#' )   
   </cfquery>
 
I even tried this:
case when '#ListERP19.EXPENSES#' is null then 0 else '#ListERP19.EXPENSES#' end,
 
And it didnt work either. The first three insert values work, basically becasue there are no NULLS in those three values. Any help/guidance would be greatly appreciated
 
ASKER CERTIFIED SOLUTION
Amir Azhdari
IT Specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 9 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 9 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004