rutledgj
asked on
Need a good approach for inserting/updating records
I'm currently using MS SQL Server 2005. Inside a stored proc, I will be using a Linked Server to pull data from a mysql db. Once I get the data I need to call one of two existing stored procs to update or insert a patient.
My question is what is the best way to do this as far as looping through the list of patients I get from the mysql db and calling the stored procs?
I always hear cursors are the wrong approach so what is a better way to do this? The one thing I need to do is after each stored proc call is to check the returned results and update a column in the original mysql table for that record. Does that regulate me to looping and doing one record at a time?
Is using a cursor the same as using a while loop?
Thanks
My question is what is the best way to do this as far as looping through the list of patients I get from the mysql db and calling the stored procs?
I always hear cursors are the wrong approach so what is a better way to do this? The one thing I need to do is after each stored proc call is to check the returned results and update a column in the original mysql table for that record. Does that regulate me to looping and doing one record at a time?
Is using a cursor the same as using a while loop?
Thanks
8080_Diver
You shouldn't need to "loop through" your list of patients. Instead, pull the data into a staging table. Then you can INNER JOIN your staging data table to the existing data table and UPDATE any matches. After that, you can LEFT OUTER JOIN your staging table to the existing data table and INSERT any rows where the existing data table's entry ID is NULL.
1) Load the data from your linked server to a temp table
2) Use the below statement to insert/update the data in your target table.
2) Use the below statement to insert/update the data in your target table.
UPDATE t1
SET t1.Column1 = t2.Column1,
t1.Column2 = t2.Column2
FROM Target_Table t1
JOIN Temp_Table t2
ON t1.PrimaryKeyColumn = t2.PrimaryKeyColumn
INSERT Target_Table
SELECT * FROM Temp_Table t1
WHERE NOT EXISTS (SELECT 1 FROM Target_Table as t2 WHERE t1.PrimaryKeyColumn = t2.PrimaryKeyColumn)ASKER
But I need a way to verify that each row was successful to update the original mysql table. How would that work in this scenario?
>> But I need a way to verify that each row was successful to update the original mysql table. How would that work in this scenario?
Do you have additional column to track this information. Can you provide your table structure?
Do you have additional column to track this information. Can you provide your table structure?
Which are you updating, the mySQL or the SQL Server database? I was operating under the assumption that the updates were going into the SQL Server database. Do you also need to update the mySQL database?
As to verification of the updates, I am not sure what you are meaning there. If you issue an UPDATE query, then either it does or it doesn't update all of the data based upon the constraints/inputs to the query. Similarly, if you issuean INSERt, it either INSERTS all it has or none of what it has.
If you get the number of rows in the mySQL table and then add the number of rows UPDATED to the number of rows INSERTED, the sum should equal the number of rows in the mySQL table. That should prove that all of the mySQL rows were used to either UPDATE or INSERT the data.
As to verification of the updates, I am not sure what you are meaning there. If you issue an UPDATE query, then either it does or it doesn't update all of the data based upon the constraints/inputs to the query. Similarly, if you issuean INSERt, it either INSERTS all it has or none of what it has.
If you get the number of rows in the mySQL table and then add the number of rows UPDATED to the number of rows INSERTED, the sum should equal the number of rows in the mySQL table. That should prove that all of the mySQL rows were used to either UPDATE or INSERT the data.
ASKER
I guess what I'm asking is if I'm updating a set of records vs one at a time using a loop, if one fails, don't they all fail? And if that is the case, there isn't an easy way to determine which record has the problem.
The insert/update occurs in sql server but I have a status field in the mysql table (where the records originally came from) that I want to update to indicate that those records have been processed so they won't get picked up again.
The insert/update occurs in sql server but I have a status field in the mysql table (where the records originally came from) that I want to update to indicate that those records have been processed so they won't get picked up again.
In SP if error come it fails fully. To come out from this you need to use error handling try and catch.
As per my opinion, best way to do using SSIS package.
As per my opinion, best way to do using SSIS package.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.