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