CFQuery question on comparing data

[Gates_001]

HI,

I have a MSSQL database and i will recive a .CSV file everyday to update it, so i'm trying to compare both of them to find out what record to update or add.

So i created a text DNS for the CSV file in COLDFUSION admin.

I linked it to the path :

<Cfset FilePath = "c:\my csv path\#theclient_number#.csv">

<cfset theclient_number = #client number from a login info#>

I query the MSSQL Database to get the current inventory

<cfquery name="get_oldinfo" datasource="#mydns#">
    SELECT *
    FROM inventory

    WHERE client = #theclient_number#
</cfquery>

<cfquery name="Total_Data_from_csv" datasource="#mycsvdns#">
    SELECT *
    FROM #filePath#
</cfquery>

I query the CSV file to get the new info and compare it to the MSSQL one for INSERT for new records.

<cfquery name="New_Data_from_csv" datasource="#mycsvdns#">
    SELECT *
    FROM #filePath#
    WHERE csv_product_number <> '#get_oldinfo.prod_number#'  <--  i think my problem is here

</cfquery>

I query the CSV file to get the info and compare it to the MSSQL one for updates in price and other stuff.

<cfquery name="Update_Data_from_csv" datasource="#mycsvdns#">
    SELECT *
    FROM #filePath#
    WHERE csv_product_number = '#get_oldinfo.prod_number#'
</cfquery>

I simply loop on both Query to either Update or Insert the info that part works but...

<cfloop query="Update_Data_from_csv" startRow="1" endRow="#Update_Data_from_csv.recordcount#">

<CFQUERY Update ...... OR INSERT  bla bla bla
</cfloop>

It inserts the data and updates it but the record count dont change so it inserts everything everytime.

get_oldinfo.recordcount =  19

Total_Data_from_csv.recordcount =  21

New_Data_from_csv.recordcount = 18     <------  thats impossible if i ran it more then once

Update_Data_from_csv.recordcount = 3

I can run it 3 times it will always say the same thing.

thanks for your help

[Dan_Bracuk]

A simpler approach would be to insert every record from your file into a working table.  Then you should be able to run a single update query and a single insert query.  The necessary logic would be in your where clause.

If you need to keep track of how many records were added or updated, a couple of select count(*)s will do the trick.

[JR__Bob__Dobbs]

I'll add couple comments to the reccomendation from Bracuk.  You might consider the following:

1. Use the BULK INSERT statement to insert the records from the CSV into a staging table.

http://msdn.microsoft.com/en-us/library/ms188365.aspx

2. You could use the MERGE statment to handle both INSERTs and UPDATEs.

http://msdn.microsoft.com/en-us/library/bb510625.aspx

3. You could move this logic out of ColdFusion and into SQL Server integration services (SSIS).  SSIS may perform better when dealing with large recordsets.  This will let you avoid looping over large CSV files to perform updates.