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
