Insert values from tableB into tableA if they do not exist

2

Normally, when working with a insert on tableA if it is based on select of a tableB, we do something similar to this:

INSERT INTO tabelaA ( column1, column2, someInt, someVarChar )
SELECT  tabelaB.column1, tabelaB.column2, 8, 'some string etc.'
FROM    tabelaB

However, my problem is that I need to validate if the data no longer exists in tableA, because if they exist, insert will not be done

    
asked by anonymous 28.04.2015 / 15:27

3 answers

2

Something like this:

INSERT INTO tabelaA (column1, column2, someInt, someVarChar )
SELECT  tabelaB.column1, tabelaB.column2, 8, 'some string etc.'
FROM    tabelaB
WHERE (SELECT count(Id) FROM tabelaA as valida WHERE valida.column1 = tabelaB.column1) < 1
    
28.04.2015 / 15:44
2

It looks like this:

INSERT INTO tabelaA ( column1, column2, someInt, someVarChar )
SELECT  tabelaB.column1, tabelaB.column2, 8, 'some string etc.'
FROM    tabelaB
where not exists (select 1 
                  from tabelaA a2
                  where a2.column1 = tabelaB.column1
                  and s2.column2 = tabelaB.column2
                  and ...)

I put the ellipsis because I do not know all the conditions, but just complete with more conditions (or simply remove and ... ).

    
28.04.2015 / 15:42
1

You have not specified which DBMS you are using. However, here is an alternative to SQL Server that is often faster than the responses that have already been posted.

MERGE tabelaA AS Target
USING (SELECT column1, column2, someInt, someVarChar FROM tabelaB) AS Source
   ON (Target.column1 = Source.column1 AND Target.column2 = Source.column2 AND (...))
WHEN NOT MATCHED BY TARGET THEN
    INSERT (column1, column2, someInt, someVarChar)
    VALUES (Source.column1, Source.column2, Source.someInt, Source.someVarChar)

This form is also more flexible if, in the future, you decide that you need to, for example, update the records in the target table if they already exist. This can be done as follows:

MERGE tabelaA AS Target
USING (SELECT column1, column2, someInt, someVarChar FROM tabelaB) AS Source
   ON (Target.column1 = Source.column1 AND Target.column2 = Source.column2 AND (...))
WHEN NOT MATCHED BY TARGET THEN
    INSERT (column1, column2, someInt, someVarChar)
    VALUES (Source.column1, Source.column2, Source.someInt, Source.someVarChar)
WHEN MATCHED THEN
    UPDATE SET Target.column1= Source.column1
    
28.04.2015 / 16:09