We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Need a good approach for inserting/updating records

rutledgj
rutledgj asked
on
Medium Priority
270 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

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.
SharathData Engineer
CERTIFIED EXPERT

Commented:
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

Author

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?
SharathData Engineer
CERTIFIED EXPERT

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?

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.

Author

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.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
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.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.