CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Saeid
Saeid

Reputation: 13592

Transfer data between two database and check not repeated values synchronously

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?

Upvotes: 1

Views: 184

Answers (2)

MatBailie
MatBailie

Reputation: 86798

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.

Upvotes: 1

Arion
Arion

Reputation: 31249

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

Upvotes: 0

Related Questions