ABMc6wAT

1BEGIN TRAN 
2--------------------------------------------------------------------------------
3--- DROP TEMP IF EXISTS
4--------------------------------------------------------------------------------
5IF Object_id('tempdb..#TempPhoneNSW', 'U') IS NOT NULL 
6  DROP TABLE #tempphonensw 
7
8IF Object_id('tempdb..#TempSource', 'U') IS NOT NULL 
9  DROP TABLE #tempsource 
10
11--------------------------------------------------------------------------------
12--- GET EMAIL FROM TABLE PHONE WITH COMMUNICATION TYPE (COMM_TYP_EMAIL
13--- , COMM_TYP_OFFICE_EMAIL)
14--------------------------------------------------------------------------------
15SELECT 
16PhoneId,
17Num
18INTO   #tempphonensw 
19FROM   phones 
20WHERE  communicationtypeid IN (SELECT communicationtypeid 
21                               FROM   communicationtypes 
22                               WHERE  SystemCode IN( 'COMM_TYP_EMAIL'
23													, 'COMM_TYP_OFFICE_EMAIL' )) 
24
25--------------------------------------------------------------------------------
26--- CREATE EMAIL DUMMY WITH @MAILINATOR
27--------------------------------------------------------------------------------
28
29SELECT phoneid, 
30       num = 'candidate' 
31             + CONVERT(NVARCHAR(10), Row_number() OVER( ORDER BY phoneid)) 
32             + '@mailinator.com' 
33INTO   #tempsource 
34FROM   #tempphonensw 
35
36--------------------------------------------------------------------------------
37--- UPATE FIELD NUM ON TABLE PHONE USING #tempsource
38--------------------------------------------------------------------------------
39
40UPDATE a 
41SET    a.num = b.num 
42FROM   phones a 
43       INNER JOIN #tempsource b 
44               ON b.phoneid = a.phoneid
45
46--------------------------------------------------------------------------------
47--- CHECK LATEST RESULT DATA  
48--------------------------------------------------------------------------------
49SELECT 
50PhoneId,
51ObjectID,
52CommunicationTypeId,
53Num,
54NumTrimmed,
55Comments,
56CreatedUserId,
57CreatedOn,
58UpdatedUserId,
59PhoneNumStripped,
60UpdatedOn
61FROM   phones 
62WHERE  communicationtypeid IN (SELECT communicationtypeid 
63                               FROM   communicationtypes 
64                                WHERE  SystemCode IN( 'COMM_TYP_EMAIL'
65													, 'COMM_TYP_OFFICE_EMAIL' )) 
66ORDER BY PhoneId ASC
67
68ROLLBACK TRAN