共好合同— 目的將 AM8數(shù)據(jù)庫(kù)表的好友，轉(zhuǎn)入到 OIOrg表，這個(gè)sql 需要在 oiorg 表環(huán)境下運(yùn)行? （先在am8數(shù)據(jù)庫(kù)運(yùn)行同名）
— 得到用戶(hù)所有分組，分組成員版本初始為 1
INSERT INTO OI_ORG_CONTACT_GROUP ( S_OWNER, G_ID, S_NAME, N_MEMBER_VER )
SELECT c.S_OWNER, c.G_GROUPID, g.S_NAME, 1
FROM [AM8].[dbo].[OI_STK_CUSTOMVIEW] c
INNER JOIN [AM8].[dbo].[OI_STK_CUSTOMGROUP] g ON g.G_GROUP = c.G_GROUPID
GROUP BY c.S_OWNER, c.G_GROUPID, g.S_NAME
ORDER BY c.S_OWNER, c.G_GROUPID
— 得到所有用戶(hù)聯(lián)系人，會(huì)過(guò)濾掉所在分組已經(jīng)沒(méi)有的數(shù)據(jù)
INSERT INTO OI_ORG_CONTACT ( S_OWNER, S_USER_LOGIN, G_GROUP_ID )
SELECT c.S_OWNER, c.S_USERLOGIN, c.G_GROUPID
FROM [AM8].[dbo].[OI_STK_CUSTOMVIEW] c
WHERE c.S_USERLOGIN <> ”
AND (G_GROUPID IN ( Select G_GROUP From [AM8].[dbo].[OI_STK_CUSTOMGROUP] )
ORG_GROUPID = ‘{00000000-0000-0000-0000-000000000000}’ )
ORDER BY c.S_OWNER, c.G_GROUPID, c.S_USERLOGIN
— 得到人員的聯(lián)系人版本號(hào)
UPDATE OI_ORG_T008 SET N_C41 = b.N_VER
FROM ( SELECT S_OWNER, Max( N_VER ) AS N_VER
? FROM [AM8].[dbo].[OI_STK_CUSTOMVIEW]
? GROUP BY S_OWNER ) b
WHERE S_C1 = b.S_OWNER
— 人員的聯(lián)系人分組版本號(hào)，有分組就置 1
UPDATE OI_ORG_T008 SET N_C42 = 1 WHERE S_C1 IN ( Select S_OWNER From OI_ORG_CONTACT_GROUP )
— 清除 AM8 數(shù)據(jù)表中的 聯(lián)系人 數(shù)據(jù)
— TRUNCATE TABLE [AM8].[dbo].[OI_STK_CUSTOMVIEW]
— GO
— TRUNCATE TABLE [AM8].[dbo].[OI_STK_CUSTOMGROUP]
— GO
