SharePoint (WSS) Portal Email Merge

This is used to copy email addresses from a WSS portal and insert them into another database using a cursor.

use SP_Content_db

SELECT right(tp_Login,9) as 'login'

     , tp_Title

     , tp_Email as 'EmailAddress'    

     , tp_ExternalTokenLastUpdated

     , e.EmployeeID, CurrentEmployee

     , e.firstname, e.lastname

     , TMSv4.dbo.fn_EmployeeName(e.EmployeeID) as 'EmployeeName'

INTO #SharePoint

FROM SP_Content_db.dbo.UserInfo ui RIGHT OUTER JOIN Employees e ON right(ui.tp_Login,9) COLLATE DATABASE_DEFAULT =e.EmployeeID

WHERE tp_email COLLATE DATABASE_DEFAULT NOT LIKE 'webmaster%'

     AND tp_login COLLATE DATABASE_DEFAULT NOT LIKE '%.%'

     AND Right(tp_Login,10) COLLATE DATABASE_DEFAULT LIKE ':%'

--     AND currentemployee = 1

DECLARE @EmployeeID varchar(9)

DECLARE @EmailAddress varchar(100)

-- ########################################################################

DECLARE myCursor CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT EmployeeID, EmailAddress

FROM #SharePoint

OPEN myCursor

     FETCH NEXT FROM myCursor

     INTO @EmployeeID, @EmailAddress

    

     WHILE @@FETCH_STATUS = 0

     BEGIN    

         

          UPDATE TMSv4.dbo.Employees

          SET EmailAddress = @EmailAddress

          WHERE EmployeeID = @EmployeeID

                                     

          -- Get the next record.

          FETCH NEXT FROM myCursor

          INTO @EmployeeID, @EmailAddress

     END

CLOSE myCursor

DEALLOCATE myCursor

drop table #SharePoint