SQL合并进程卡住了。

huangapple go评论75阅读模式
英文:

SQL Merge into getting stuck

问题

我想将一些数据从Python的pandas数据框合并到我的运行在Azure上的SQL Server 2019中。

这是我的代码及示例数据值:

MERGE INTO [dbo].[APPSCHED_CNG] AS t
USING (
    VALUES
        (53243, '2023-06-19 12:39:35', 20230621, 80000, 20230621, 90000, 0, 0),
        (53259, '2023-06-19 12:39:35', 20230621, 123000, 20230621, 133000, 0, 0),
        (53266, '2023-06-19 12:39:35', 20230621, 93000, 20230621, 103000, 0, 0),
        (53234, '2023-06-19 12:39:35', 20230621, 80000, 20230621, 80000, 0, 0),
        (53286, '2023-06-19 12:39:35', 20230621, 80000, 20230621, 120000, 0, 0),
        (53272, '2023-06-19 12:39:35', 20230621, 123000, 20230621, 153000, 0, 0),
        (51509, '2023-06-19 12:39:35', 20230621, 74500, 20230621, 74500, 0, 0),
        (53247, '2023-06-19 12:39:35', 20230621, 80000, 20230621, 110000, 0, 0),
        (53255, '2023-06-19 12:39:35', 20230621, 80000, 20230621, 80000, 0, 0),
        (53236, '2023-06-19 12:39:35', 20230621, 93000, 20230621, 123000, 0, 0),
        (53251, '2023-06-19 12:39:35', 20230621, 93000, 20230621, 113000, 0, 0),
        (52915, '2023-06-19 12:39:35', 20230621, 100000, 20230621, 110000, 0, 0),
        (53238, '2023-06-19 12:39:35', 20230621, 100000, 20230621, 120000, 0, 0),
        (53239, '2023-06-19 12:39:35', 20230621, 123000, 20230621, 143000, 0, 0),
        (53284, '2023-06-19 12:39:35', 20230621, 143000, 20230621, 153000, 0, 0)
) AS s ([KEY], [change], [ANNDAT], [ANNZEIT], [ENDDAT], [ENDZEIT], [RG], [DATAGRP])
ON t.[KEY] = s.[KEY] AND t.[ANNDAT] = s.[ANNDAT] AND t.[ANNZEIT] = s.[ANNZEIT] AND t.[ENDDAT] = s.[ENDDAT] AND t.[ENDZEIT] = s.[ENDZEIT] AND t.[DATAGRP] = s.[DATAGRP]
WHEN NOT MATCHED THEN
    INSERT ([KEY], [change], [ANNDAT], [ANNZEIT], [ENDDAT], [ENDZEIT], [RG], [DATAGRP])
    VALUES (s.[KEY], s.[change], s.[ANNDAT], s.[ANNZEIT], s.[ENDDAT], s.[ENDZEIT], s.[RG], s.[DATAGRP]);

它应该做什么:
我有来自IBM db2数据库的这些数据。当我打开前端时,后端应该检查DB2是否有新的数据集。如果数据集不在Azure-SQL上,那么新的数据集应该被插入 - 除非只有在RG方面有差异,如果有差异,不同的RG就不相关。

这是表格脚本:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[APPSCHED_CNG](
    [KEY] [int] NULL,
    [change] [datetime] NULL,
    [ANNDAT] [int] NULL,
    [ANNZEIT] [int] NULL,
    [ENDDAT] [int] NULL,
    [ENDZEIT] [int] NULL,
    [RG] [int] NULL,
    [DATAGRP] [bit] NULL
) ON [PRIMARY]
GO

有时它可以工作,有时会导致SSMS一直加载,看起来找不到结束点。

对于我的代码有什么问题?
没有支持WHEN MATCHED THEN会有问题吗?

谢谢。

英文:

I want to Merge into some data from a (python) panda dataframe to my sql-server 2019 running on azure.

This is my code with example data values:

MERGE INTO [dbo].[APPSCHED_CNG] AS t
USING (
    VALUES
        (53243, '2023-06-19 12:39:35', 20230621, 80000, 20230621, 90000, 0, 0),(53259, '2023-06-19 12:39:35', 20230621, 123000, 20230621, 133000, 0, 0),(53266, '2023-06-19 12:39:35', 20230621, 93000, 20230621, 103000, 0, 0),(53234, '2023-06-19 12:39:35', 20230621, 80000, 20230621, 80000, 0, 0),(53286, '2023-06-19 12:39:35', 20230621, 80000, 20230621, 120000, 0, 0),(53272, '2023-06-19 12:39:35', 20230621, 123000, 20230621, 153000, 0, 0),(51509, '2023-06-19 12:39:35', 20230621, 74500, 20230621, 74500, 0, 0),(53247, '2023-06-19 12:39:35', 20230621, 80000, 20230621, 110000, 0, 0),(53255, '2023-06-19 12:39:35', 20230621, 80000, 20230621, 80000, 0, 0),(53236, '2023-06-19 12:39:35', 20230621, 93000, 20230621, 123000, 0, 0),(53251, '2023-06-19 12:39:35', 20230621, 93000, 20230621, 113000, 0, 0),(52915, '2023-06-19 12:39:35', 20230621, 100000, 20230621, 110000, 0, 0),(53238, '2023-06-19 12:39:35', 20230621, 100000, 20230621, 120000, 0, 0),(53239, '2023-06-19 12:39:35', 20230621, 123000, 20230621, 143000, 0, 0),(53284, '2023-06-19 12:39:35', 20230621, 143000, 20230621, 153000, 0, 0)
) AS s ([KEY], [change], [ANNDAT], [ANNZEIT], [ENDDAT], [ENDZEIT], [RG], [DATAGRP])
ON t.[KEY] = s.[KEY] AND t.[ANNDAT] = s.[ANNDAT] AND t.[ANNZEIT] = s.[ANNZEIT] AND t.[ENDDAT] = s.[ENDDAT] AND t.[ENDZEIT] = s.[ENDZEIT] AND t.[DATAGRP] = s.[DATAGRP]
WHEN NOT MATCHED THEN
    INSERT ([KEY], [change], [ANNDAT], [ANNZEIT], [ENDDAT], [ENDZEIT], [RG], [DATAGRP])
    VALUES (s.[KEY], s.[change], s.[ANNDAT], s.[ANNZEIT], s.[ENDDAT], s.[ENDZEIT], s.[RG], s.[DATAGRP]);

What it should do:
I have this data from a IBM db2 database. When I open the frontend, the backend should check DB2 for new datasets. If the datasets are not on the Azure-SQL, the new datasets should be inserted - exept, if there is a difference only at RG, so a different RG is not relevant if there is a difference.

And here is the table script:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[APPSCHED_CNG](
	[KEY] [int] NULL,
	[change] [datetime] NULL,
	[ANNDAT] [int] NULL,
	[ANNZEIT] [int] NULL,
	[ENDDAT] [int] NULL,
	[ENDZEIT] [int] NULL,
	[RG] [int] NULL,
	[DATAGRP] [bit] NULL
) ON [PRIMARY]
GO

Sometimes it works, sometimes it causes the SSMS to load and load and load and it looks it do not find an end..

What seems to be the problem with my code?
Is it a problem that I did not support WHEN MATCHED THEN?

Thank you

答案1

得分: 0

INSERT INTO SELECT WHERE NOT EXISTS as written in the comments brings the solution.
Here updated code:

INSERT INTO [dbo].[APPSCHED_CNG] ([KEY], [change], [ANNDAT], [ANNZEIT], [ENDDAT], [ENDZEIT], [RG], [DATAGRP])
SELECT s.[KEY], s.[change], s.[ANNDAT], s.[ANNZEIT], s.[ENDDAT], s.[ENDZEIT], s.[RG], s.[DATAGRP]
FROM (
    VALUES
        (51509, '2023-06-19 15:59:31', 20230621, 80000, 20230621, 74500, 0, 0),
        (53247, '2023-06-19 15:57:31', 20230621, 80000, 20230621, 110000, 0, 0)
        --rest of values			
) AS s ([KEY], [change], [ANNDAT], [ANNZEIT], [ENDDAT], [ENDZEIT], [RG], [DATAGRP])
WHERE NOT EXISTS (
    SELECT 1
    FROM [dbo].[APPSCHED_CNG] t
    WHERE t.[KEY] = s.[KEY]
        AND t.[ANNDAT] = s.[ANNDAT]
        AND t.[ANNZEIT] = s.[ANNZEIT]
        AND t.[ENDDAT] = s.[ENDDAT]
        AND t.[ENDZEIT] = s.[ENDZEIT]
        AND t.[DATAGRP] = s.[DATAGRP]
);
英文:

INSERT INTO SELECT WHERE NOT EXISTS as written in the comments brings the solution.
Here updated code:

INSERT INTO [dbo].[APPSCHED_CNG] ([KEY], [change], [ANNDAT], [ANNZEIT], [ENDDAT], [ENDZEIT], [RG], [DATAGRP])
                        SELECT s.[KEY], s.[change], s.[ANNDAT], s.[ANNZEIT], s.[ENDDAT], s.[ENDZEIT], s.[RG], s.[DATAGRP]
                        FROM (
                            VALUES
                                (51509, '2023-06-19 15:59:31', 20230621, 80000, 20230621, 74500, 0, 0),
								(53247, '2023-06-19 15:57:31', 20230621, 80000, 20230621, 110000, 0, 0)
								--rest of values			
                        ) AS s ([KEY], [change], [ANNDAT], [ANNZEIT], [ENDDAT], [ENDZEIT], [RG], [DATAGRP])
                        WHERE NOT EXISTS (
                            SELECT 1
                            FROM [dbo].[APPSCHED_CNG] t
                            WHERE t.[KEY] = s.[KEY]
                                AND t.[ANNDAT] = s.[ANNDAT]
                                AND t.[ANNZEIT] = s.[ANNZEIT]
                                AND t.[ENDDAT] = s.[ENDDAT]
                                AND t.[ENDZEIT] = s.[ENDZEIT]
                                AND t.[DATAGRP] = s.[DATAGRP]
                        );

huangapple
  • 本文由 发表于 2023年6月19日 19:00:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/76505988.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定