英文:
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]
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论