Home / Questions / 365984
Accepted
Hackerman
Asked: 2020-06-17 11:40:15 +0800 CST 2020-06-17 11:40:15 +0800 CST

Insert data from one table to another without duplicates

  • 772

I have two tables in different databases with the same structure, in database1 the information arrives at one of the tables, the information that arrives at this table I have to pass to the table of database2 .

I have the following script that does this without any problem, no matter how many times I execute it, it does not enter duplicates, it only enters the records that are in database1 and that are not in database2

INSERT INTO [Datos1].dbo.lotesInsert (Codlote,Animal,Peso)
SELECT *
FROM [Datos2].dbo.lotesInsert T1
 WHERE NOT EXISTS (SELECT NULL
                     FROM [Datos1].dbo.lotesInsert T2
                    WHERE t2.Codlote = t1.Animal)

This is how the data stays when I run the script, it sends them to the other database without problem, if I insert another animal in database1, that is, the next one, it only sends that one, it does not duplicate the data, it only sends those that do not exist in the database2 " This
is how the information arrives in database2"

enter image description here

But when I change the batch it duplicates the previous batch, in the image we see that if there is a batch it inserts it fine without duplicates without problem, but I insert batch 18101 it duplicates batch 18100
"This is how it is in the database1"

enter image description here

here I already run the script with the data of the new batch, the new batch inserts it well, but it duplicates the previous
one "so they reach the database2"

enter image description here

I have a solution for this or an explanation of why it happens and thus solve it

sql

1 Answers

  1. Best Answer

    From what I basically understand, you have the detail in the SELECT(which is finally the one that "decides" which elements will be taken to pass to the INSERT; now I'll tell you, since you want it not to be repeated CodLoteand AnimalI suggest the following Query (focusing on it SELECTfor place it where you already have it):

        SELECT *
    FROM [Datos2].dbo.lotesInsert T1
     WHERE CONCAT(T1.CodLote, '~', CAST(T1.Animal AS VARCHAR(15))) 
           NOT IN 
           (SELECT CONCAT(T2.CodLote, '~',  CAST(T2.Animal AS VARCHAR(15)))
                         FROM [Datos1].dbo.lotesInsert T2)

    The character ~is just a separator that I like to use. I suggest you leave it or place one of your preference.

    If you need to, do not include it, and if it matches , add the separator and the WeightPeso field to both CONCATs in the parts of T1 and T2.

    • 1