Newbie trying to decipher merge command associated code

Question

Someone retired in our group and I'm trying to figure out what his merge statement (and associated code) does so I can determine how to convert some (not all) values to integer before sending up. See comments below for questions. I am an absolute newbie with Microsoft SQL and took a class in php a few years ago, but don't have much experience. I've tried googling the merge command but I'm having trouble with a couple parts in it. See my questions below. (// ?) I've looked at:

  http://php.net/manual/en/pdo.query.php
  http://stackoverflow.com/questions/4336573/merge-to-target-columns-using-source-rows
  http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fsqlp%2Frbafymerge.htm

I realize these are basic questions but I'm trying to figure it out and nobody around here knows.

function storeData ($form)
{
global $ms_conn, $QEDnamespace;
//I'm not sure what this is doing??  I thought this was where it was sending data up??
$qry = "MERGE INTO visEData AS Target
    USING (VALUES (?,?,?,?,?,?,?,?,?,?))
           AS Source (TestGUID,pqID, TestUnitID, TestUnitCountID,
           ColorID, MeasurementID, ParameterValue,
           Comments, EvaluatorID, EvaluationDate)
    ON Target.pqID = Source.pqID 
        AND Target.MeasurementID=Source.MeasurementID  //what is this doing? 
        AND Target.ColorID=Source.ColorID  //what is target and source? 
    WHEN MATCHED THEN
        UPDATE SET ParameterValue = Source.ParameterValue,
            EvaluatorID = Source.EvaluatorID, //where is evaluatorID and source? My table or table we're send it to?
            EvaluationDate = Source.EvaluationDate,
            Comments = Source.Comments
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (TestGUID,
            pqID, TestUnitID, TestUnitCountID,
            ColorID, MeasurementID,
            ParameterValue, Comments,
            EvaluatorID, EvaluationDate, TestIndex, TestNumber) 
        VALUES (Source.TestGUID, Source.pqID, 
                       Source.TestUnitID, 
                       Source.TestUnitCountID,
           Source.ColorID, Source.MeasurementID, Source.ParameterValue,
           Source.Comments, Source.EvaluatorID, Source.EvaluationDate,?,?);";

$pqID = coverSheetData($form);
$tid = getBaseTest($form['TextField6']);
$testGUID = getTestGUID($tid);
$testIndex = getTestIndex ($testGUID);
foreach ($form['visE']['parameters'] as $parameter=>$element)
{
    foreach ($element as $key=>$data)
    {
        if ( mb_ereg_match('.+evaluation', $key) === true )
        {
            $testUnitData = getTestUnitData ($form, $key, $tid, $testGUID);
            try
            {
               //I'm not sure if this is where it's sent up?? 
               //Maybe I could add the integer conversion here??
               $ms_conn->query ($qry, array(
                 $testGUID, $pqID,
                 $testUnitData[0], $testUnitData[1], $testUnitData[2],$element['parameterID'], $data, $element['comments']  $QEDnamespace->userid, date ('Y-m-d'), $testIndex, $tid));
                                     }
            catch (Zend_Db_Statement_Sqlsrv_Exception $e)
            {
                dataLog($e->getMessage());
                returnStatus ("Failed at: " . $key);
            }
        }
    }
}

}

i guess there is a reason he "retired", are you sure he wasn´t fired about that code..? — luk2302, Jun 05 '13 at 13:36
No, he retired. I think it's more complicated because of other historical databases that don't let you name items, plus there's a cube structure that someone using the DB had to have simple tables that had single id's linking to other tables. I don't know the details. — Michele, Jun 06 '13 at 17:01

score 0 · Answer 1 · answered Jun 05 '13 at 14:53

This is a bit long for a comment. If you are using SQL Server, then look at the SQL Server documentation on merge. All the SQL Server documentation is on line, and it is very easy to find via Google (and perhaps even easier using Bing).

The purpose of the MERGE command is to do both inserts and updates in one step. Basically, you have a table that has new data ("source") and a table to be updated ("target"). When a record matches, then update the existing record in the target with matching record in source. When a record doesn't match, then insert it into target.

The main advantage of MERGE over two statements is not necessarily the elegant and intuitively obvious syntax. The main advantage is that all the operations occur in a single transaction, so either they all succeed or all fail as one.

The syntax actually isn't that bad. I would recommend that you set up a test database and try a few examples on your own, so you at least understand the syntax. Then, return to this code. When doing so, print out the resulting merge statement and put it in SQL Server Management Studio, where you will have nice color coded key words for the statement. Then go through it step by step, and you'll probably find that it makes lots of sense.

Newbie trying to decipher merge command associated code

1 Answers1