Prev: Collect csv from ftp server and bulk import
Next: Link server Identity column insert question?
From: deeg on 30 Jun 2010 15:55
Thanks for your assistance. However the code that was needed to
accomplish the task follows:

CREATE TABLE #tmpTable
( Email_Address VARCHAR(50),
)

INSERT INTO
#tmpTable
SELECT DISTINCT
EMAIL_ADDRESS
FROM VisitorEmail
WHERE EMAIL_ID = 0
AND DATE_ENTERED BETWEEN @FROM_DATE AND @TO_DATE
AND @INCLUDE_REJECT_DETAIL = 1
AND LEN(LTRIM(RTRIM(EMAIL_ADDRESS))) > 0
ORDER BY EMAIL_ADDRESS

SELECT (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) AS ID,
ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) AS ROWNUM,
EMAIL_ADDRESS
INTO #tmpEmail
FROM #tmpTable

SELECT t1.EMAIL_ADDRESS,
t2.EMAIL_ADDRESS,
t3.EMAIL_ADDRESS
FROM #tmpEmail t1
JOIN #tmpEmail t2 on t2.ROWNUM = t1.ROWNUM + 1
JOIN #tmpEmail t3 on t3.ROWNUM = t1.ROWNUM + 2
WHERE t1.ID = 1

DROP TABLE #tmpTable
DROP TABLE #tmpEmail

This issue has been resolved.
First  |  Prev  | 
Pages: 1 2
Prev: Collect csv from ftp server and bulk import
Next: Link server Identity column insert question?