I’m integrating other systems to play sms and importing users from sql server table to playsms mysql. My inserts are fine and they populate play sms, but never been visible in play sms web ui. What i’m missin here?
Here’s how I do the UPSERTs from integration db table and my sql tables
-------------- T-SQL CODE---------
with SourceFiltered AS
(
Select a.id uid, a.AccountID gpid, a.EMailAddress email , a.FirstName + ’ ’ + a.LastName as [Name], a.MobilePhone [mobile]
from autotask.Contact a join ad.AdWhitelist w on
RIGHT(a.EMailAddress, LEN(a.EMailAddress) - CHARINDEX(’@’, a.EMailAddress)) = w.[Organization Name]
where Active = 1 and MobilePhone <> ‘’ and len(MobilePhone) > 4
and isnumeric(right(MobilePhone, 4)) = 1
–order by AccountID
)
MERGE INTO [sms].[playsms_featurePhonebook] AS target
USING SourceFiltered AS source
ON (target.uid = source.uid AND
target.email = source.email AND
target.[Name] = source.[Name])
WHEN MATCHED THEN
UPDATE SET
[id]= source.[uid],
[uid]= source.[uid],
[email] = source.[email],
[Name] = source.[Name],
[mobile] = source.[mobile],
[tags] = ‘MERGE UPDATED’
WHEN NOT MATCHED THEN
INSERT (c_timestamp
,[id]
,[uid]
,[email]
,[Name]
,[mobile]
,[tags])
VALUES (‘111111’,
source.[uid],
source.[uid],
source.[email],
source.[Name],
source.[mobile],
‘MERGE INSERT’)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action,
DELETED.[uid] AS Target_uid,
DELETED.[email] AS Target_email,
DELETED.[Name] AS Target_Name,
INSERTED.[uid] AS Source_uid,
INSERTED.[email] AS Source_email,
INSERTED.[Name] AS Source_Name;