March 22, 2012 at 6:59 am
Hello,
Sorry if this topic already exists but I did not find it.
I have a table Allocation used to make a link between the tables Companies and Projects .
So the different columns of my tables are :
CodeProject ,
CodeCompany,
infos,
infos2,
....
The primary key is composed from the two columns CodeProjet and CodeCompany.
When I received data from an external supplier I have to remove from his table the combinaison of codeProjet, codeCompany already existing in my table(Tab_Allocation) to avoid a duplicate primary key.
Insert into Tab_Affectation (CodeProjet, CodeIntervenant, Signature )
select distinct projectID , investorId ,'PTZA' as signature from ImportPortugal
where investorId is not null and ....
normally I write a ProjectID not in (select ProjectID from Tab_Projet) but here I do not know how to check a key of two columns)
How to write a (CodeProjet,CodeCompany) NOT IN .....
I hope it is quite clear.
thanks for your help .
PS : I have very few data to insert so performance is not an issue.
March 22, 2012 at 7:31 am
You use LEFT JOIN or NOT EXISTS:
INSERT DestinationTable (...)
SELECT ...
FROM AnotherSource s
LEFT JOIN DestinationTable d
ON d.CodeProject = s.CodeProject
AND d.CodeCompany = s.CodeCompany
WHERE d.CodeProject IS NULL
-- Or the same with NOT EXISTS
INSERT DestinationTable (...)
SELECT ...
FROM AnotherSource s
WHERE NOT EXISTS (SELECT 1 FROM DestinationTable d
WHERE d.CodeProject = s.CodeProject
AND d.CodeCompany = s.CodeCompany)
March 22, 2012 at 7:47 am
thanks !
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply