merge语句临时表 - 如何将数据导入本地数组?

We're having a tough time debugging because our retired predecessor has a query statement that puts pulled data into a series of temporary tables before uploading to the database. It's not working now (we added items to TestPatterns table, and running for tp7 instead of tp21 so data may differ) and we're having a tough time debugging it. If we could somehow print or access the data put in the temporary tables, maybe we could debug it better. Any ideas on how to debug this better, and maybe see the data? We can print our first select/where statement, but not the data it pulls when the qry is executed. Plus there's the Source/Target sql merges we do. We know everything seems to work until we execute the $qry1d.

I looked online for how to view this info, and see the sql profiler, but I'm worried the statements are too complex to decipher the profiler output. When I tried to profile withhttp://youtu.be/mJ8Dyv4Uk6E, for a simple select top 1000 rows, it said:

 exec sp_executesql N'SELECT
clmns.name AS [Name]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
WHERE
(CAST(clmns.is_sparse AS bit)=@_msparam_0)and((tbl.name=@_msparam_1 and SCHEMA_NAME(tbl.schema_id)=@_msparam_2))
ORDER BY
clmns.column_id ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'Measurements',@_msparam_2=N'dbo'

It's failing with

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

This is a couple of the php sql merge statements:

$qry = 'SELECT "PrintSamples"."PrintSampleID", "PrintSamples"."TestPatternName", "PrintSamples"."PrintCopyID", 
"DigitalImages"."CaptureTime", "PrintSampleAnalyses"."psaTicket", "Measurements"."MeasurementID", "Measurements"."MeasurementUuid", 
SUBSTRING("OperatorLastName",1,1) AS "lastInitial", SUBSTRING("OperatorFirstName",1,1) AS "firstInitial",
"ParameterValues"."ParameterID", "ParameterName", "TargetName", "ParameterValues"."ParameterValue"
FROM "ParameterValues"
LEFT JOIN "Measurements" ON "ParameterValues"."MeasurementID"="Measurements"."MeasurementID" 
LEFT JOIN "PrintSampleAnalyses" ON "PrintSampleAnalyses"."psaID"="Measurements"."psaID"
LEFT JOIN "DigitalImages" ON "DigitalImages"."ImageID"="PrintSampleAnalyses"."ImageID" 
LEFT JOIN "PrintSamples" ON "DigitalImages"."PrintSampleID"="PrintSamples"."PrintSampleID"
LEFT JOIN "Sessions" ON "Sessions"."SessionID"="PrintSampleAnalyses"."SessionID" 
LEFT JOIN "Operators" ON "Operators"."OperatorID"="Sessions"."OperatorID"
LEFT JOIN "ParameterNames" ON "ParameterNames"."ParameterID"="ParameterValues"."ParameterID"
LEFT JOIN "Targets" ON "Targets"."TargetID"="Measurements"."TargetID"
WHERE ('; 

//----------------------------------------------------------------------------------------------------
// The two statements where added to make AvgGhostValAsSir77 and MaxNegGhostingValAsSir77 work.
// After much testing it was found that the query did not recognize these to ParameterNames in the 
// Postgres database. It was never discovered why when testing the ParameterName to be equal to
// AvgGhostValAsSir77 or MaxNegGhostingValAsSir77 the query fails but using the LIKE statement 
// corrected the problem. It is possible that the names contain a hidden character or space that
// caused the proble. More investigation will need to be done to find a better resolution to this 
// strange problem. On 8/1/13 - It was found that the two Parameters AvgGhostValAsSir77 and MaxNegGhostingValAsSir77
// have a trailing space to thier names in the Postgres database and that's why these two parameters
// were NOT working. It was decided instead of having IQAF people modify the database that the two
// statements using the LIKE will remain in place. The file was modified on this date 8/1/13.
//----------------------------------------------------------------------------------------------------
// only take values that actually get reported on the dashboard
// this list comes from the "MeasurementNames" table
foreach ($measurementIDs as $mid){
    if($mid[0] == "AvgGhostValAsSir77") $qry .= '(("ParameterName" LIKE ' . "'%AvgGhostValAsSir77%'" . ') AND ("TargetName"=' . "'" . $mid[1] . "')) OR "; 
    else if($mid[0] == "MaxNegGhostingValAsSir77") $qry .= '(("ParameterName" LIKE ' . "'%MaxNegGhostingValAsSir77%'" . ') AND ("TargetName"=' . "'" . $mid[1] . "')) OR "; 
    else $qry .= '(("ParameterName"=' . "'" . $mid[0] . "'" . ') AND ("TargetName"=' . "'" . $mid[1] . "')) OR ";
}    
$qry = substr ($qry, 0, -4);
$qry .= ") ";

if (isset($captureTime)){   // used for incremental updates
    $qry .= ' AND ("CaptureTime">' . "'" . $captureTime . "')";
}
// steve invalid reading code is -99999. 
$qry .= ' AND ("ParameterValues"."ParameterValue" != -99999) ORDER BY "PrintSampleID", "MeasurementID"';

$actionString = '$action';

$qryCreate = "CREATE TABLE #tmpMeasurementTable (TestGUID uniqueidentifier, 
            IQAFid uniqueidentifier, pqID int, MeasurementID int,
            EvaluationDate datetime, EvaluatorID int, 
            TestUnitID int, TestUnitCountID int,
            TestPatternID int, ColorID int,
            TargetID int, ParameterID int,
            ParameterValue real)
            CREATE TABLE #MergeOutput (ActionType nvarchar(10))";
//start putting data into measurement tables
$qry1a= "INSERT INTO #tmpMeasurementTable VALUES ";
//put 1a data in MeasurementData
$qry1b = "
    MERGE INTO MeasurementData AS Target
    USING #tmpMeasurementTable AS Source 
    ON Target.pqID=Source.pqID 
        AND Target.MeasurementID=Source.MeasurementID  
        AND Target.TargetID=Source.TargetID  
        AND Target.ParameterID=Source.ParameterID 
        AND Target.TestPatternID=Source.TestPatternID 
        AND Target.IQAFMeasurementID=Source.IQAFid  
    WHEN MATCHED THEN
        UPDATE SET Target.ParameterValue = Source.ParameterValue,
                Target.TestUnitID=Source.TestUnitID, 
                Target.TestUnitCountID=Source.TestUnitCountID,
                Target.EvaluationDate=Source.EvaluationDate,  
                Target.EvaluatorID=Source.EvaluatorID  
    WHEN NOT MATCHED BY Target THEN
        INSERT (TestGUID, IQAFMeasurementID, pqID, 
                MeasurementID, EvaluationDate, EvaluatorID, 
                TestUnitID, TestUnitCountID, TestPatternID, ColorID,
                TargetID, ParameterID, ParameterValue)
        VALUES (Source.TestGUID, Source.IQAFid, Source.pqID, 
                Source.MeasurementID, Source.EvaluationDate, Source.EvaluatorID, 
                Source.TestUnitID, Source.TestUnitCountID,
                Source.TestPatternID, Source.ColorID, 
                Source.TargetID, Source.ParameterID, Source.ParameterValue)
    OUTPUT 
        $actionString INTO #MergeOutput;
        DROP TABLE #tmpMeasurementTable";

$qryOutput = "SELECT ActionType, COUNT(ActionType)AS [count] FROM #MergeOutput GROUP BY ActionType";
$qryCleanup = "DROP TABLE #MergeOutput";

$qry1c = "IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MergeOutput') AND xtype='U')
            DROP TABLE #MergeOutput
    CREATE TABLE #MergeOutput (ActionType nvarchar(10))
    SELECT TestIndex AS TestID, TestID AS TestNumber, MeasurementData.TestGUID, pqID, TestUnitID, TestUnitCountID, TestPatternID,
  ColorID, MeasurementData.MeasurementID, TargetID, ParameterID,
  CAST(ROUND(AVG(ParameterValue*Multiplier), 2)AS DECIMAL(18,2)) AS Value
  INTO #tmpTable
  FROM MeasurementData
  LEFT JOIN Measurements ON Measurements.MeasurementID=MeasurementData.MeasurementID 
  LEFT JOIN Tests ON Tests.TestGUID=MeasurementData.TestGUID AND Tests.PiggybackID IS NULL 
  WHERE MeasurementData.TestGUID='" . $TestGUID . "' AND pqID>=" . $startPQid . " AND pqID<=" .$endPQid;


if (isset($captureTime))    // used for incremental updates
{
    $qry1c .= " AND EvaluationDate>'" . makeDateTime($captureTime) . "'";
}

$qry1c .= " GROUP BY TestIndex, TestID, MeasurementData.TestGUID, pqID, TestUnitID, TestUnitCountID, TestPatternID,
  ColorID, MeasurementData.MeasurementID, TargetID, ParameterID
  ORDER BY ColorID, TestPatternID, TestUnitCountID, MeasurementData.MeasurementID, TargetID, ParameterID;
  SELECT @@ROWCOUNT AS rows INTO #DashboardRows;";

//put temporary table in dashboard table
$qry1d = ";
    MERGE INTO DashboardData AS Target
    USING #tmpTable AS Source
    ON Target.pqID=Source.pqID 
        AND Target.MeasurementID=Source.MeasurementID  
        AND Target.TargetID=Source.TargetID  
        AND Target.ParameterID=Source.ParameterID 
        AND Target.TestPatternID=Source.TestPatternID 
    WHEN MATCHED THEN
        UPDATE SET Target.ParameterValue=Source.Value,
                Target.TestUnitID=Source.TestUnitID, 
                Target.TestUnitCountID=Source.TestUnitCountID 
    WHEN NOT MATCHED BY Target THEN
        INSERT (TestGUID, pqID, 
                MeasurementID, 
                TestUnitID, TestUnitCountID, TestPatternID, ColorID,
                TargetID, ParameterID, ParameterValue,
                TestNumber, TestIndex)
        VALUES (Source.TestGUID, Source.pqID,
                Source.MeasurementID,
                Source.TestUnitID, Source.TestUnitCountID,
                Source.TestPatternID, Source.ColorID, 
                Source.TargetID, Source.ParameterID, Source.Value, 
                Source.TestNumber, Source.TestID)
     OUTPUT 
        $actionString INTO #MergeOutput;
    DROP TABLE #tmpTable";

The queries get executed like this:

 $result = $ms_conn->query($qry1c);     $recordCount = $ms_conn->fetchOne
 ("SELECT rows FROM #DashboardRows");   //dataLog ("OLAP database " .
 $recordCount . " records to process", true);   $result =
 $ms_conn->query($qry1d); dataLog ("end dashboard query");

1个回答



要查看中间表,请从表名中删除第一个字符#</ code>。 这将使表不是临时的,因为SQL Server的惯例是,如果表的名称以此字符开头,则表是临时的。 您可能还想从代码中删除 drop table </ code>语句。 然后你可以像对待任何其他表一样调查这些中间表。</ p>

当然,为了能够重新运行代码,你必须手动删除表。</ p>
</ div>

展开原文

原文

To look at the intermediate tables, remove the first character # from the table names. This will make the tables non temporary, as SQL Server ha the convention that a table is temporary if its name starts with this character. You might also want to remove the drop table statements from the code. Then you can just look into these intermediate tables like into any other table.

Of course, to be able to re-run the code, you would have to drop the tables manually.

dtc4547
dtc4547 如果您正在SQL Management Studio中查看新表,则必须刷新树或更高版本的Tables分支以查看新表。
6 年多之前 回复
dougou8573
dougou8573 我以为我会加上这个花絮。 我带走了#tmpTable和tmpMeasurementTable,但是当我运行代码时,我没有在SQL数据库中看到它。 即便在我们精神焕发之后。 然后我的同事发现如果我们关闭数据并重新打开它就会出现。 搞定! 谢谢!
6 年多之前 回复
dst2017
dst2017 好吧,如果你省略了CREATE TABLE中的#,你必须在MERGE ... USING中省略它,因为表名只是不同。 它必须是一致的。
接近 7 年之前 回复
doubinduo3364
doubinduo3364 不会这样做,所以下一个查询失败,因为它找不到表? 如果我删除了#,它正在使用的表在哪里?
接近 7 年之前 回复
dpmfur2635
dpmfur2635 只需删除所有'#'。
接近 7 年之前 回复
dounao1875
dounao1875 我很难决定删除哪个#,因为有很多#并且它们在下一个语句中使用,所以如果删除#,它将使其余的代码无效。
接近 7 年之前 回复
douhao2721
douhao2721 是的,可能省略'#'。 但您也可以通过Management Studio中的GUI执行此操作:在对象资源管理器中(通常在左侧),打开数据库,打开“表”节点,选择表(您可能必须选择“表”节点一个命中 F5刷新表列表以查看它,并点击Del键。 然后确认删除。
接近 7 年之前 回复
douao7937
douao7937 谢谢@FrankPl! 我们试一试。 如果我们想手动删除表,我们是否需要转到新的SQL查询并输入,例如“DROP TABLE #MergeOutput”?
接近 7 年之前 回复
立即提问
相关内容推荐