FETCH NEXT的使用

原表結構:
HR_NewsPaper
------------------
id
CompanyName
CompanyInfo
PositionName
PositionNumber
Requirement
Note

為了排除冗餘資料,將此表分為兩表,表Company用於儲存公司相關資訊,表HRNewsPaper用於儲存招聘資訊. 
HRNewsPaper表:
------------------
id
CompanyId
PositionName
PositionNumber
Requirement
InfoFrom
UpdateTime

Company表:
------------------
id
CompanyName
CompanyInfo
Note
InfoFrom
UpdateTime

排除原表中關於Company的冗餘資料的方法有三(僅列出關鍵語句):
1. select distinct companyName into tmp from HR_Newspaper

2. select id, CompanyName, CompanyInfo, Note, getDate() as UpdateTime into CompanyNew from HR_Newspaper t1
where checksum(*) = (select top 1 checksum(*) from HR_Newspaper where CompanyName = t1.CompanyName)

3. select  min(id) as id, CompanyName, min(CompanyInfo) as CompanyInfo, min(Note) as Note, getdate() as UpdateTime into NewCompany
 from HR_Newspaper  group   by   CompanyName

使用FETCH NEXT迴圈取出相關資料並插入新表:
DECLARE @CompanyName varchar(256)
declare @CompanyInfo varchar(4000)
declare @PositionName varchar(60)
declare @PositionNumber int
declare @Requirement varchar(4000)
declare @Note varchar(4000)
DECLARE @id int
DECLARE @tmpHrId int
declare @counter int
DECLARE @i int
set @id = 1
set @i = 1
select @counter = count(CompanyName) from HR_NewsPaper

DECLARE hr_cursor CURSOR FOR
SELECT id, CompanyName,CompanyInfo,PositionName,PositionNumber,Requirement,Note FROM HR_NewsPaper

OPEN hr_cursor

FETCH NEXT FROM hr_cursor
INTO @id, @CompanyName,@CompanyInfo,@PositionName,@PositionNumber,@Requirement,@Note

WHILE @@FETCH_STATUS = 0
BEGIN

   PRINT Convert(varchar(4), @id) + ','+  @CompanyName +','+ @PositionName
   select @i = id from COMPANY where CompanyName = @companyName
--插入資料
   INSERT INTO HRNewsPaper (CompanyId, PositionName, PositionNumber, Requirement)
   VALUES (@i, @PositionName, @PositionNumber, @Requirement)
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM hr_cursor
   INTO @id, @CompanyName,@CompanyInfo,@PositionName,@PositionNumber,@Requirement,@Note
END

CLOSE hr_cursor
DEALLOCATE hr_cursor