1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
| ALTER PROCEDURE [ITS_selectLdapUsers]
AS
BEGIN
SET NOCOUNT ON;
-- 3.2 Insert / update data into gobal temp database
MERGE em_temp c
USING (
SELECT
SAMAccountName as em_id
,givenname AS name_first
,sn AS name_last
,mail AS email
,[net-user-name] AS net_user_name
,SUBSTRING(department,0,16) as dp_id
,ADsPath AS OU
--,SUBSTRING(ADsPath,LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)+1)),LEN(ADsPath)-LEN(RIGHT(ADsPath,20))-LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)))) AS OU
FROM OPENQUERY(ADSI,'<LDAP://root.blabla:3268/DC=root,DC=blabla,DC=blibli>;(&(objectCategory=person) (objectClass=user) (givenname=f*) );SAMAccountName,givenName,sn,mail,net-user-name,department,ADsPath;subtree')
) s
ON s.em_id COLLATE DATABASE_DEFAULT = c.em_id COLLATE DATABASE_DEFAULT
WHEN MATCHED THEN
UPDATE
SET name_first=s.name_first
, name_last=s.name_last
, email=s.email
,net_user_name = s.net_user_name
,dp_id=s.dp_id
,OU =s.ou
WHEN NOT MATCHED BY TARGET THEN
INSERT (
em_id
,name_first
,name_last
,email
,net_user_name
,dp_id
,ou
)
VALUES (
s.em_id
,s.name_first
,s.name_last
,s.email
,s.net_user_name
,s.dp_id
,s.ou
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action, Inserted.*, Deleted.*;
END |
Partager