合并语句临时表-如何将数据定向到本地数组? [英] merge statement temporary table - how to direct data into a local array?

问题描述

我们的调试工作很艰难，因为我们退休的前任有一条查询语句，该语句将提取的数据放入一系列临时表中，然后再上传到数据库中.现在无法正常工作(我们向TestPatterns表中添加了项目，并运行了tp7而不是tp21，因此数据可能会有所不同)，并且调试起来很困难.如果我们能够以某种方式打印或访问临时表中的数据，也许我们可以对其进行更好的调试.关于如何更好地调试它的任何想法，也许还可以查看数据?我们可以打印第一个select/where语句，但不能打印执行qry时提取的数据.另外，还有我们执行的源/目标sql合并. 我们知道在执行$ qry1d之前一切似乎都可以正常工作.

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.

我在网上寻找如何查看此信息，并查看sql探查器，但是我担心语句太复杂而无法破译探查器输出.当我尝试使用 http://youtu.be/mJ8Dyv4Uk6E 进行配置时，只需选择前1000行，说:

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'

失败

MERGE语句尝试更多地更新或删除同一行 不止一次.当目标行与多个源匹配时，就会发生这种情况 排. MERGE语句无法更新/删除目标的同一行 表多次.优化ON子句以确保目标行 最多匹配一个源行，或使用GROUP BY子句进行分组 源行.

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.

这是几个php sql merge语句:

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";

查询将像这样执行:

 $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");

推荐答案

要查看中间表，请从表名称中删除第一个字符#.这将使表成为非临时表，因为SQL Server约定如果表的名称以该字符开头，则该表为临时表.您可能还想从代码中删除drop table语句.然后，您可以像浏览其他任何表一样查看这些中间表.

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.

这篇关于合并语句临时表-如何将数据定向到本地数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！