# Database Discussions > Microsoft SQL Server 2005 >  Copy Record and duplicate to a new table

## 02119

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

----------


## 02119

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

 :Big Grin: 
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 :Big Grin:

----------

