Results 1 to 2 of 2

Thread: Copy Record and duplicate to a new table

  1. #1
    Join Date
    Dec 2008
    Posts
    2

    Unhappy Copy Record and duplicate to a new table

    Hi I'mk new to the site and hope you can help...

    I have inherited a databse, and its a mess.

    I have one main problem that I have been unable to resolve.

    I have 1 table with records in for sickness

    ie

    IdNo / Name / DateFrom / DateTo / NoOfDaysSick
    02119 / Anyone / 01/04/08 / 05/04/08 / 5

    What I need to do is to copy this data to another table but put one entry in for each day sick

    ie

    IdNo / Name / Date
    02119 / Anyone / 01/04/08
    02119 / Anyone / 02/04/08
    02119 / Anyone / 03/04/08
    02119 / Anyone / 04/04/08
    02119 / Anyone / 05/04/08

    I'm new to SQl so any help would be very greatful

  2. #2
    Join Date
    Dec 2008
    Posts
    2

    Resolved this issue

    I have managed to resolve this issue... thanks any way


    declare @counter int
    declare @SickRecIDNo int
    declare @DateFrom datetime
    declare @DateTo datetime
    declare @WorkingDaysOff int
    declare @Diagnosis nvarchar(100)
    declare @DatesonCert nvarchar(50)
    declare @LastDayShift bit
    declare @Weekend bit
    declare @EmployeeIDNo int
    declare @SickCodeIDNo int
    declare @SickRecordComment varchar(8000)
    declare @TypeCertIDNo int
    declare @LTSCert bit
    declare @EOYrCodingIDNo int
    declare @HoursLost decimal(18, 2)
    declare @Accident bit
    declare @TotalLTSDays int

    -- Declare the cursor
    declare mycursor cursor for
    select * from [Sickness Records]

    -- Open the cursor
    open mycursor
    fetch next from mycursor
    into @SickRecIDNo,@DateFrom,@DateTo,@WorkingDaysOff,@Di agnosis,@DatesonCert,@LastDayShift,@Weekend,@Emplo yeeIDNo,@SickCodeIDNo,@SickRecordComment,@TypeCert IDNo,@LTSCert,@EOYrCodingIDNo,@HoursLost,@Accident ,@TotalLTSDays

    -- begin statements and select
    while @@FETCH_STATUS = 0
    begin
    set @counter=1
    while @counter<=@WorkingDaysOff
    begin
    insert SicknessRecords_Advanced
    select @SickRecIDNo,dateadd(day,@counter,@DateFrom),@Date To,@WorkingDaysOff,@Diagnosis,@DatesonCert,@LastDa yShift,@Weekend,@EmployeeIDNo,@SickCodeIDNo,@SickR ecordComment,@TypeCertIDNo,@LTSCert,@EOYrCodingIDN o,@HoursLost,@Accident,@TotalLTSDays
    set @counter=@counter+1
    end
    fetch next from mycursor
    into @SickRecIDNo,@DateFrom,@DateTo,@WorkingDaysOff,@Di agnosis,@DatesonCert,@LastDayShift,@Weekend,@Emplo yeeIDNo,@SickCodeIDNo,@SickRecordComment,@TypeCert IDNo,@LTSCert,@EOYrCodingIDNo,@HoursLost,@Accident ,@TotalLTSDays
    end
    -- close the cursor and deallocate
    close mycursor
    deallocate mycursor

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •