SQL: Copy data- sp_CopyCourse

ALTER proc [dbo].[sp_CopyCourse]
(
@CurrentYear Int,
@Department varchar(50),
@CurrentStdSession varchar(50),
@CurrentStdBatch varchar(50),
@TargetStdSession varchar(50),
@TargetStdBatch varchar(50)
)
as

—- exec sp_CopyCourse 1,’ME’,’1986-1990′,’84’,’1986-1990′,’83’

begin
DECLARE @tmpCourseId varchar(50), @CourseId varchar(50) , @TargetTerm int ,@TargetYear int ,@TargetDepartmentId varchar(20), @CourseCode varchar(20),@CourseTitle varchar(100)
,@CourseCredit decimal(8,2), @CourseCreditHours decimal(8,2),@TutorialCreditHours decimal(8,2), @CourseType varchar(20),@TargetBatch varchar(50)
,@Session varchar(50),@CourseOrder int

declare CopyCourse cursor for

SELECT CourseId
,TargetTerm
,TargetYear
,TargetDepartmentId
,CourseCode
,CourseTitle
,CourseCredit
,CourseCreditHours
,TutorialCreditHours
,CourseType
,TargetBatch
,[Session]
,CourseOrder
FROM T_Common_Course_Information
WHERE [Session]=@CurrentStdSession AND TargetBatch=@CurrentStdBatch AND TargetYear=@CurrentYear

open CopyCourse

fetch next from CopyCourse into
@CourseId,@TargetTerm,@TargetYear,@TargetDepartmentId, @CourseCode,@CourseTitle,@CourseCredit,@CourseCreditHours, @TutorialCreditHours,@CourseType,@TargetBatch,@Session,@CourseOrder

while @@FETCH_STATUS = 0
begin

SELECT @tmpCourseId=REPLACE(@CourseId,@CurrentStdBatch,@TargetStdBatch)

INSERT INTO T_Common_Course_Information
(
CourseId
,TargetTerm
,TargetYear
,TargetDepartmentId
,CourseCode
,CourseTitle
,CourseCredit
,CourseCreditHours
,TutorialCreditHours
,CourseType
,TargetBatch
,[Session]
,CourseOrder
)
VALUES
(
@tmpCourseId,
@TargetTerm,
@TargetYear,
@TargetDepartmentId,
@CourseCode,
@CourseTitle,
@CourseCredit,
@CourseCreditHours,
@TutorialCreditHours,
@CourseType,
@TargetStdBatch,
@TargetStdSession,
@CourseOrder
)

fetch next from CopyCourse into
@CourseId,@TargetTerm,@TargetYear,@TargetDepartmentId, @CourseCode,@CourseTitle,@CourseCredit,@CourseCreditHours, @TutorialCreditHours,@CourseType,@TargetBatch,@Session,@CourseOrder
end
close CopyCourse
deallocate CopyCourse

end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s