June 10, 2014 at 1:32 am
I tried to make an insert query that adds all the values of a dummy table which are not yet in the main db
I tried this:
INSERT INTO artikelen (PartNrFabrikant, omschrijving, verkoopprijs, EAN)
SELECT (PartNrFabrikant, omschrijving, verkoopprijs, EAN)
FROM artikelen
INNER JOIN [Hofstede].[dbo].[SparePartsUpdate] SP
SET [toegevoegd] = getDate()
WHERE a.[PartNrFabrikant] not in sp.[PartNrFabrikant]
but that is never going to work :p
what should I change?
June 10, 2014 at 1:40 am
Take a look at the following MSDN page on how to use (NOT) IN, because once more, you forgot the subquery.
Anyway, you will not find missing values when you use an INNER JOIN.
Try the following:
INSERT INTO artikelen (PartNrFabrikant, omschrijving, verkoopprijs, EAN)
SELECT PartNrFabrikant, omschrijving, verkoopprijs, EAN
FROM [Hofstede].[dbo].[SparePartsUpdate] SP
WHERE NOT EXISTS (SELECT 1 FROM artikelen AT WHERE AT.PartNrFabrikant = SP.PartNrFabrikant);
An alternative using LEFT OUTER JOIN:
INSERT INTO artikelen (PartNrFabrikant, omschrijving, verkoopprijs, EAN)
SELECT PartNrFabrikant, omschrijving, verkoopprijs, EAN
FROM [Hofstede].[dbo].[SparePartsUpdate] SP
LEFT OUTER JOIN artikelen AT ON AT.PartNrFabrikant = SP.PartNrFabrikant
WHERE AT.PartNrFabrikant IS NULL;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 10, 2014 at 5:45 am
nearly perfect, runs like a charm! haha thanks :p
June 10, 2014 at 7:15 am
pk2dpvp (6/10/2014)
nearly perfect, runs like a charm! haha thanks :p
No problem 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply