两个数据库之间传输数据并同步检查不重复值

发布于 2025-01-08 04:12:31 字数 564 浏览 0 评论 0原文

我想在来自不同数据库的 2 个表之间传输一些数据，但源表在 PostalCode 列中有一些重复的值，我在 PostalCode 列和传输中创建带有 UK 的目标表脚本需要同步检查以不插入之前插入的值，这是我的示例脚本：

INSERT INTO [Target]
(
    [FirstName],
    [LastName],
    [PostalCode],
)
(
SELECT  
[Sc].[FirstName],
[Sc].[LastName],
CASE 
WHEN 'Check for not repeated before' THEN [Sc].[PostalCode]
ELSE CAST(1000000000 + ROW_NUMBER() OVER(ORDER BY [Sc].[FirstName]) AS CHAR(10)) END

FROM [Source] AS [Sc]
);

那么，您对处理这个问题有什么建议？

编辑

有没有办法用 for 或光标编写脚本？我的意思是异步检查重复值？

原文

I want transfer some data between 2 tables from difference DBs But the source table have a some repeated values in PostalCode column, I create target table with UK on PostalCode column and in transfer script need to check synchronously to not insert a value that inserted before, this is my sample script:

INSERT INTO [Target]
(
    [FirstName],
    [LastName],
    [PostalCode],
)
(
SELECT  
[Sc].[FirstName],
[Sc].[LastName],
CASE 
WHEN 'Check for not repeated before' THEN [Sc].[PostalCode]
ELSE CAST(1000000000 + ROW_NUMBER() OVER(ORDER BY [Sc].[FirstName]) AS CHAR(10)) END

FROM [Source] AS [Sc]
);

So, what is your suggestion to handle this?

Edit

And is there any way to write an script with for or cursor? I mean check repeated Values asynchronously?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寄人书 2025-01-15 04:12:31

我强烈建议不要将两条信息混合到一个字段中。

相反，只需添加一个额外的列，可能称为 DuplicationID。

INSERT INTO [Target]
(
    [FirstName],
    [LastName],
    [PostalCode],
    [DuplicationID]
)
SELECT  
    [Sc].[FirstName],
    [Sc].[LastName],
    [Sc].[PostalCode],
    ROW_NUMBER() OVER (PARTITION BY [Sc].[PostalCode] ORDER BY [Sc].[PostalCode])
FROM 
    [Soruce] AS [Sc]

DuplicationID 为 1 的任何记录都将计为该邮政编码的第一个实例。任何其他值都是重复的。

I strongly recommend against mixxing two pieces of information into a single field.

Instead, just have an extra column, possibly called DuplicationID.

INSERT INTO [Target]
(
    [FirstName],
    [LastName],
    [PostalCode],
    [DuplicationID]
)
SELECT  
    [Sc].[FirstName],
    [Sc].[LastName],
    [Sc].[PostalCode],
    ROW_NUMBER() OVER (PARTITION BY [Sc].[PostalCode] ORDER BY [Sc].[PostalCode])
FROM 
    [Soruce] AS [Sc]

Any record where DuplicationID is 1 is counted as the first instance of that postcode. Any other value is a duplicate.

回复收藏 0 原文

无力看清 2025-01-15 04:12:31

也许是这样的：

;WITH CTE AS
(
    SELECT
        COUNT(*) OVER(PARTITION BY [PostalCode]) AS NbrOf,
        ROW_Number() OVER
                    (
                        PARTITION BY [PostalCode] 
                        ORDER BY [PostalCode]
                ) AS RowNbr
        [FirstName],
        [LastName],
        [PostalCode],
    FROM
        [Source] AS [Sc]
)
INSERT INTO [Target]
(
    [FirstName],
    [LastName],
    [PostalCode],
)
SELECT  
    [Sc].[FirstName],
    [Sc].[LastName],
    CASE 
        WHEN CTE.NbrOf>1 
        THEN CAST(1000000000+CTE.RowNbr AS VARCHAR(10))
        ELSE [Sc].[PostalCode] 
    END
FROM 
    CTE

Maybe something like this:

;WITH CTE AS
(
    SELECT
        COUNT(*) OVER(PARTITION BY [PostalCode]) AS NbrOf,
        ROW_Number() OVER
                    (
                        PARTITION BY [PostalCode] 
                        ORDER BY [PostalCode]
                ) AS RowNbr
        [FirstName],
        [LastName],
        [PostalCode],
    FROM
        [Source] AS [Sc]
)
INSERT INTO [Target]
(
    [FirstName],
    [LastName],
    [PostalCode],
)
SELECT  
    [Sc].[FirstName],
    [Sc].[LastName],
    CASE 
        WHEN CTE.NbrOf>1 
        THEN CAST(1000000000+CTE.RowNbr AS VARCHAR(10))
        ELSE [Sc].[PostalCode] 
    END
FROM 
    CTE

回复收藏 0 原文

~没有更多了~