Copy Record and duplicate to a new table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copy Record and duplicate to a new table

Author

Topic

02119
Starting Member

8 Posts

Posted - 2008-12-23 : 05:06:03

Hi I'm 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

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-23 : 05:25:38

DECLARE @cnt INT
SELECT @cnt = NoofDaysSick FROM replicat

WHILE(@cnt>0)
BEGIN
INSERT INTO totable
SELECT * FROM fromtable
SELECT @cnt = @cnt - 1
END
SELECT * FROM totable

Jai Krishna

02119
Starting Member

8 Posts

Posted - 2008-12-23 : 05:51:32

Jai

This seems to of worked a treat...

Thankyou very much for your help...

I'm just checking the data now...

Once again thanks

Steve

02119
Starting Member

8 Posts

Posted - 2008-12-23 : 06:45:42

Hi Jai

I've looked at this and its not quite what I was after..

When it copies the data across I need the Datefrom to increase by one day for each NoOfSickDays or untill DateFrom = DateTo

So if one person is of for 5 days (NoOfSickDays)

Then the result should show
5 entries with the dateFrom increasing by 1 day

Hope this reads correct...

Steve

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-23 : 08:01:51

Don't know how to do it without using a cursor (because cursor is very slow)
but this example works and you can adapt it to your environment.

-- Create some sample data
create table #srce_test(
col1 varchar(10),
testdate datetime,
testdays int
)
create table #dest_test(
col1 varchar(10),
testdate datetime
)
insert #srce_test
select 'record1', getdate()-10, 5 union all
select 'record2', getdate()-300, 7

select * from #srce_test
-- End sample data

-- Here begins my solution
declare @counter int
declare @cur_col1 varchar(10)
declare @cur_testdate datetime
declare @cur_testdays int

declare mycursor cursor for
select * from #srce_test

open mycursor
fetch next from mycursor
into @cur_col1,@cur_testdate,@cur_testdays

while @@FETCH_STATUS = 0
begin
 set @counter=1
 while @counter<=@cur_testdays
 begin
  insert #dest_test
  select @cur_col1,dateadd(day,@counter,@cur_testdate)
  set @counter=@counter+1
 end
 fetch next from mycursor
 into @cur_col1,@cur_testdate,@cur_testdays
end
close mycursor
deallocate mycursor

select * from #dest_test
drop table #dest_test
drop table #srce_test

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

02119
Starting Member

8 Posts

Posted - 2008-12-23 : 09:17:03

Thank you Webfred

this is just what was needed

this is a copy of my finished code

Steve
-----

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 mycursor cursor for
select * from [Sickness Records]

open mycursor
fetch next from mycursor
into @SickRecIDNo,@DateFrom,@DateTo,@WorkingDaysOff,@Diagnosis,@DatesonCert,@LastDayShift,@Weekend,@EmployeeIDNo,@SickCodeIDNo,@SickRecordComment,@TypeCertIDNo,@LTSCert,@EOYrCodingIDNo,@HoursLost,@Accident,@TotalLTSDays

while @@FETCH_STATUS = 0
begin
set @counter=1
while @counter<=@WorkingDaysOff
begin
insert SicknessRecords_Advanced
select @SickRecIDNo,dateadd(day,@counter,@DateFrom),@DateTo,@WorkingDaysOff,@Diagnosis,@DatesonCert,@LastDayShift,@Weekend,@EmployeeIDNo,@SickCodeIDNo,@SickRecordComment,@TypeCertIDNo,@LTSCert,@EOYrCodingIDNo,@HoursLost,@Accident,@TotalLTSDays
set @counter=@counter+1
end
fetch next from mycursor
into @SickRecIDNo,@DateFrom,@DateTo,@WorkingDaysOff,@Diagnosis,@DatesonCert,@LastDayShift,@Weekend,@EmployeeIDNo,@SickCodeIDNo,@SickRecordComment,@TypeCertIDNo,@LTSCert,@EOYrCodingIDNo,@HoursLost,@Accident,@TotalLTSDays
end
close mycursor
deallocate mycursor

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-23 : 09:20:22

Welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-23 : 11:15:44

quote:

Originally posted by webfred

Don't know how to do it without using a cursor (because cursor is very slow)
but this example works and you can adapt it to your environment.

-- Create some sample data
create table #srce_test(
col1 varchar(10),
testdate datetime,
testdays int
)
create table #dest_test(
col1 varchar(10),
testdate datetime
)
insert #srce_test
select 'record1', getdate()-10, 5 union all
select 'record2', getdate()-300, 7

select * from #srce_test
-- End sample data

-- Here begins my solution
declare @counter int
declare @cur_col1 varchar(10)
declare @cur_testdate datetime
declare @cur_testdays int

declare mycursor cursor for
select * from #srce_test

open mycursor
fetch next from mycursor
into @cur_col1,@cur_testdate,@cur_testdays

while @@FETCH_STATUS = 0
begin
 set @counter=1
 while @counter<=@cur_testdays
 begin
  insert #dest_test
  select @cur_col1,dateadd(day,@counter,@cur_testdate)
  set @counter=@counter+1
 end
 fetch next from mycursor
 into @cur_col1,@cur_testdate,@cur_testdays
end
close mycursor
deallocate mycursor

select * from #dest_test
drop table #dest_test
drop table #srce_test

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Slight modification to webfred solution because the current code will not give what 02119 ( author) wants exactly..

-- Create some sample data
create table #srce_test(
col1 varchar(10),
testdate datetime,
testdays int
)
create table #dest_test(
col1 varchar(10),
testdate datetime
)
insert #srce_test
select 'record1', getdate()-10, 5 union all
select 'record2', getdate()-300, 7

select * from #srce_test
-- End sample data

-- Here begins my solution
declare @counter int
declare @cur_col1 varchar(10)
declare @cur_testdate datetime
declare @cur_testdays int

declare mycursor cursor for
select * from #srce_test

open mycursor
fetch next from mycursor
into @cur_col1,@cur_testdate,@cur_testdays

while @@FETCH_STATUS = 0
begin
set @counter=0
while @counter<@cur_testdays
begin
insert #dest_test
select @cur_col1,dateadd(day,@counter,@cur_testdate)
set @counter=@counter+1
end
fetch next from mycursor
into @cur_col1,@cur_testdate,@cur_testdays
end
close mycursor
deallocate mycursor

select * from #dest_test
drop table #dest_test
drop table #srce_test

Note : Updated set @counter=0 from set @counter=1
and while @counter<@cur_testdays from while @counter<=@cur_testdays

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-23 : 13:51:19

raky eagle eye thx

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources