Insert values from table B in table A if they do not exist

Asked

Viewed 183 times

2

Usually when we work with a insert table based on the select from a tabelaB, 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, the insert will not be done

sql

  • Maybe a WHERE NOT EXISTS Xyz ?

  • @Rafaelwithoeft Would you kindly demonstrate how the structure would look?

  • I found an example for you, I hope it helps: http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table

3 answers

2

Stay 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 was reluctant because I do not know all the conditions, but just complete with more conditions (or simply remove the and ...).

2


Something like that:

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

1

You haven’t specified which BDS you’re using. However, here is an alternative to SQL Server that is often faster than the answers 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

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.