Hello All,

I need to know where I might find a good resource on Stored Procedures to compare two records. Basically I will need to check if the records match. If they are differant I will need to run and update on that particular row (on one of the tables that is being compared). If it does not match any results it will need to be added.

Can't how to go about this. I am writing this SQL SP against and ASA database.

-Nick

So I think my last post was a bit vague. Hopefully someone will know what I'm talking about with some clarification.

1-I need to create a cursor that is populated with data from a flat file (CSV).

*/I believe I have completed this already. I can't seem to find a way to return values stored in the cursor.. so I could be wrong. *\

2- I need to then compare the records stored in my cursor against records stored at a table. This is a table that has Automobile data and I should be able to use a VIN number to complete the comparison.

3- If a record matches in the cursor to the record in the table no changes are needed. However, if the record at the table doesn't exist, it needs to be inserted. If the VIN is found but any of the other fields that make up that particular record are differant than I need to perform an Update.

So I know I need a cursor to store flat file data. But I can't find adaqute information on performing the check that I need done. How do I look through the cursor and compare to the other table? Documentation basically shows how to make a cursor and populate it.

Any help would be rad.

Thanks,

Nick

A cursor really shouldn't be necessary. You should be able to load the flat file into a temp table, then either do an update query from a join, or delete the rows from your table that exist in the flat file and then do an insert.

Thank you for your reply.

An update on a join will not insert rows that do not already exist. The idea behind this stored procedure is to not delete the data that resides in my table and insert all the records from the temp table. Am I understanding you correctly?

Thanks,

Nick

An inner join will only return data that already exists in both places, but an outer - ie left or right - join will return data that exists in either table, and let you write simple formulas to determine whether any piece of data is in one table, the other, or both. A union is similar, but in a different format, and probably one that wouldn't be as useful if you want to compare things; it's easier to do that across columns than across rows.

I'm not really sure why you would want to use a database cursor? I can see how that would be an approach, but it's procedural, or step by step, instead of set based. So it's very much not what SQL was optimized for.

Without having used bulk transfers within SQL to read files in a long time, this is pseudo code, but you could accomplish what you're talking about with something like this:

-- Load CSV file into a temp table
bcp "c:\filename.csv" into #rawData

-- Update existing but different rows
Update Cars Set Field1 = csv.Field1, ... From #rawData As csv Join Cars On csv.vin = Cars.vin Where csv.Field1 != Cars.Field1 Or csv.Field2 != Cars.Field2 ...

-- Add data for automobiles that don't already have records by VIN
Insert Into Cars Select csv.* From #rawData As csv Left Join Cars On csv.vin = Cars.vin Where Cars.vin Is Null

That will save you from having to deal with a cursor, and probably be easier to maintain.