Insert not exist duplicating records

Question

tou with a problem in SQL server 2008
I have to enter an occurrence of the credit card data however the query is not taking place the way I needed.
The tables are

  CREATE TABLE TEMP_DADOS_CARTAO
        (
            TOKENID_PARCELA     VARCHAR(255)
          , CARDNUMBER     VARCHAR(255)
          , HOLDER         VARCHAR(255)
          , EXPIRATIONDATE VARCHAR(255)
          , ARQUIVO_LOG    VARCHAR(100)
        )
    ;

CREATE TABLE DADOS_CARTAO (
    ID int  IDENTITY(0,1) NOT NULL,
    TOKENID_PARCELA varchar(255)  NOT NULL,
    TOKENID varchar(255)  NOT NULL,
    CARDNUMBER varchar(255) NULL,
    HOLDER varchar(250)  NULL,
    EXPIRATIONDATE varchar(7)  NULL,
    DATA_PROCESSAMENTO datetime2(7) NULL,
    ARQUIVO_LOG varchar(100) NULL,
    CONSTRAINT DADOS_CARTAO_PK PRIMARY KEY (ID)
);   

both the Insert into not exist http://sqlfiddle.com/#! 18/d0b83/1

INSERT INTO DADOS_CARTAO
        (TOKENID_PARCELA
          , TOKENID
          , CARDNUMBER
          , HOLDER
          , EXPIRATIONDATE
          , DATA_PROCESSAMENTO
          , ARQUIVO_LOG
        )
    SELECT
        TEMP.TOKENID_PARCELA
      , SUBSTRING(TEMP.TOKENID_PARCELA, 1, 25)
      , TEMP.CARDNUMBER
      , TEMP.HOLDER
      , TEMP.EXPIRATIONDATE
      , GETDATE()
      , TEMP.ARQUIVO_LOG
    FROM
        TEMP_DADOS_CARTAO TEMP
    WHERE
        NOT EXISTS
        (
            SELECT
                1
            FROM
                DADOS_CARTAO EC
            WHERE
                EC.TOKENID =SUBSTRING(TEMP.TOKENID_PARCELA, 1, 25)
        )

how much merge http://sqlfiddle.com/#! 18/5b90b/1

MERGE DADOS_CARTAO as DC
    USING (SELECT
        T.TOKENID_PARCELA  , SUBSTRING(T.TOKENID_PARCELA, 1, 25) TOKENID, T.CARDNUMBER , T.HOLDER  , T.EXPIRATIONDATE ,T.ARQUIVO_LOG
        from TEMP_DADOS_CARTAO T ) as TEMP 
    on DC.TOKENID =TEMP.TOKENID
WHEN NOT MATCHED THEN
    INSERT (TOKENID_PARCELA
          , TOKENID
          , CARDNUMBER
          , HOLDER
          , EXPIRATIONDATE
          , DATA_PROCESSAMENTO
          , ARQUIVO_LOG)
    VALUES (TEMP.TOKENID_PARCELA
      , TEMP.TOKENID
      , TEMP.CARDNUMBER
      , TEMP.HOLDER
      , TEMP.EXPIRATIONDATE
      , GETDATE()
      , TEMP.ARQUIVO_LOG);;

are duplicating the records

I don’t know what I’m doing wrong

Answer 1

Look, on your temporary table you have 1:N. That is, each TokenId may appear several times.

If you want to enter them only once, you could group the data, taking the last record of each.

The fact is that even the MERGE and the INSERT NOT EXISTS, will end up inserting exactly what returns in your JOIN.

If you test that INSERT NOT EXISTS, without the part of INSERT, will see that there is already bringing the duplicated data... And they will not be validated line by line during the INSERT... That’s why the data ends up being entered in duplicate.

As a suggestion, I’d say group the data, but I don’t know if that’s what you’re really trying to do.

Example:

INSERT INTO DADOS_CARTAO
        (TOKENID_PARCELA
          , TOKENID
          , CARDNUMBER
          , HOLDER
          , EXPIRATIONDATE
          , DATA_PROCESSAMENTO
          , ARQUIVO_LOG
        )
    SELECT
        TEMP.TOKENID_PARCELA
      , SUBSTRING(TEMP.TOKENID_PARCELA, 1, 25)
      , TEMP.CARDNUMBER
      , TEMP.HOLDER
      , max(TEMP.EXPIRATIONDATE)
      , GETDATE()
      , max(TEMP.ARQUIVO_LOG)
    FROM
        TEMP_DADOS_CARTAO TEMP
    WHERE
        NOT EXISTS
        (
            SELECT
                1
            FROM
                DADOS_CARTAO EC
            WHERE
                EC.TOKENID =SUBSTRING(TEMP.TOKENID_PARCELA, 1, 25)
                /*se os dados já forem cadastrados, não cadastra novamente*/
        )
        group by TEMP.TOKENID_PARCELA
      , SUBSTRING(TEMP.TOKENID_PARCELA, 1, 25)
      , TEMP.CARDNUMBER
      , TEMP.HOLDER

See rotating on Sqlfiddle.