• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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
0
rutledgj
Asked:
rutledgj
  • 3
  • 2
  • 2
  • +1
1 Solution
 
8080_DiverCommented:
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.
0
 
SharathData EngineerCommented:
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.
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)

Open in new window

0
 
rutledgjAuthor Commented:
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?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SharathData EngineerCommented:
>> 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?
0
 
8080_DiverCommented:
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.
0
 
rutledgjAuthor Commented:
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.
0
 
Alpesh PatelAssistant ConsultantCommented:
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.
0
 
8080_DiverCommented:
You can, as mentioned, use a TRY...CATCH to handle the error and, although I am not entirely sure of the exact mechanism, I believe you can get the row of data that failed.  

In addition, there are a couple of approaches to the task of updating the rows from mySQL that you are using to update SQL Server.  The one I usually use is to first update the source data's flag column with something like a P (indicating Processing) and then selecting all of the rows with a P in that column as the set of source data to be used to update the target table/database.  Once the processing succeeds, I update the source data's P to something like C for Completed.  

This approach has the advantage of automatically handling the issue of dealing with a failed update.  If the update step fails, you know which source rows were involved in the processing (they'll still be marked with P's)  Also, if you determine the problem (e.g. a bad date that is carried in a string format in the source data), the next run of the package will not only pick up any new data but it will also automatically process the previous set of source data marked with P's.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now