stored procedure 資料移轉

CREATE PROCEDURE sp_LeaveDayBatch(
@year char(4)
) AS

declare @EmployID varchar(10),
@DateOnJob as datetime,
@DateAvailable as datetime,
@DateExpire as datetime,
@LeaveDaysAvailable as int

declare authors_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR

SELECT MV001,
CONVERT(datetime,MV021,112) as DateOnJob,
CONVERT(datetime,@year+right(MV021,4),112) as DateAvailable,
dateadd(day,-1,convert(datetime,CAST(CAST(@year as int) + 1 AS char(4)) + right(MV021,4),112)) as DateExpire,
dbo.getdays(year(convert(datetime, MV021, 112)), cast(@year as numeric(4))) as LeaveDaysAvailable
FROM [172.16.200.81].Leader.dbo.CMSMV

OPEN authors_cursor
FETCH NEXT FROM authors_cursor
into @EmployID,
@DateOnJob,
@DateAvailable,
@DateExpire,
@LeaveDaysAvailable

WHILE (@@FETCH_STATUS =  0 )
 BEGIN
  
 INSERT INTO CMSMV (EmployID, DateOnJob, DateAvailable, DateExpire, LeaveDaysAvailable) VALUES (@EmployID,@DateOnJob, @DateAvailable,@DateExpire, @LeaveDaysAvailable)

  FETCH NEXT FROM authors_cursor
  into @EmployID,
  @DateOnJob,
  @DateAvailable,
  @DateExpire,
  @LeaveDaysAvailable

 END 

CLOSE authors_cursor
DEALLOCATE authors_cursor

原文转自：http://www.ltesting.net