November 30, 2017 at 1:57 am
I have a need to only insert Emp_Info into Emptbl if the Emp_Info doesn't already exist, or if the Emp_Info does exist AND has a value in the Emp_His column. I used below merge statement, but I get an error "An action of type 'INSERT' is not allowed in the 'WHEN MATCHED' clause of a MERGE statement. I understand that there is a match so insert is not permitted, but I need to do insert regardless since not all column values match the destination. Can you please guide me to what would be a best way to go about it?
merge into Emptbl as T
using EmptblFileSource as S
on (T.Emp_Info = S.Emp_Info)
when matched and (t.Emp_His is not null) THEN
insert ([Emp_Name],[Emp_Phone],[Web],[Address1],[City],[State],[ZipCode])
values (s.[Emp_Name],s.[phone number], s.[website],s.[Street Address],s.[City],[State],s.[Zip Code])
WHEN NOT MATCHED BY TARGET THEN
insert ([Emp_Name],[Emp_Phone],[Web],[Address1],[City],[State],[ZipCode])
values (s.[Emp_Name],s.[phone number], s.[website],s.[Street Address],s.[City],[State],s.[Zip Code])
November 30, 2017 at 2:23 am
Since INSERT is your only course of action (part from doing nothing), I don't think I'd bother with the MERGE statement. Try something like this. If it doesn't work, it should only need minor tweaks, but if you're still struggling, please post some DDL in the form of CREATE TABLE statements and sample data in the form of INSERT statements.
INSERT INTO Emptbl (
mp_Name
, Emp_Phone
, Web
, Address1
, City
, State
, ZipCode
)
SELECT
s.Emp_Name
, s.[phone number]
, s.website
, s.[Street Address]
, s.City
, s.State
, s.Zip Code
FROM EmptblFileSource s
LEFT JOIN Emptbl t
ON s.Emp_Info = t.Emp_Info
AND s.EmpInfo IS NOT NULL
WHERE t.Emp_Info IS NULL
John
November 30, 2017 at 3:30 am
Hi John,
I agree, I figured my approach with using merge was incorrect. I am following your approach and see how it goes. Thank you for your input.
December 1, 2017 at 10:54 am
INSERT INTO Emptbl (
mp_Name
, Emp_Phone
, Web
, Address1
, City
, State
, ZipCode
)
SELECT
s.Emp_Name
, s.[phone number]
, s.website
, s.[Street Address]
, s.City
, s.State
, s.Zip Code
FROM EmptblFileSource s
LEFT JOIN Emptbl t
ON s.Emp_Info = t.Emp_Info
WHERE t.Emp_Info IS NULL
UNION
SELECT
s.Emp_Name
, s.[phone number]
, s.website
, s.[Street Address]
, s.City
, s.State
, s.Zip Code
FROM EmptblFileSource s
LEFT JOIN Emptbl t
ON s.Emp_Info = t.Emp_Info
WHERE s.EmpInfo IS NOT NULL;
December 1, 2017 at 5:16 pm
Greetings Joe, thank you for sending the query. I followed John's query, and that worked perfectly.
I was thinking of using merge, but I was wrong. I am glad I posted the question. I appreciate your reply.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply