how to fetch data from one table1 to table 2

[uva1102]

Good Day Everyone..

i have two tables..how to read from one table1 to table2..
example..
Table1 with thousand over records various types of recurrance and due pattern.

where ID = History_ID

Table 1
ID Reccurance Duepattern
1 Monthly 10
2 Yearly 25
3 Monthly 15
4 Weekly
5 yearly 14

Table 2
History_ID Datedue
Null null
null null
null null
null null
null null

Required output in table2

History_ID Date_Due
1 10/1/2011
1 10/2/2011
1 10/3/2011
1 10/4/2011
1 10/5/2011
1 10/6/2011
1 10/7/2011
1 10/8/2011
1 10/9/2011
1 10/10/2011
1 10/11/2011
1 10/12/2011

2 25/1/2011
2 25/1/2012
2 25/1/2013
2 25/1/2014
2 25/1/2015
2 25/1/2016
2 25/1/2017
2 25/1/2018
2 25/1/2019
2 25/1/2020

3 15/1/2011
3 15/2/2011
3 15/3/2011
3 15/4/2011
3 15/5/2011
3 15/6/2011
3 15/7/2011
3 15/8/2011
3 15/9/2011
3 15/10/2011
3 15/11/2011
3 15/12/2011

Please guide me..really appreciate..
Thanks and regards.

[uva1102]

table2 should be based on recurrance and date_due from table 1.
ID = History_ID

[Netcode]

Are you saying you want to move all data from table one to table two?

[BitBlt]

Had to do this with a cursor, and I wasn't sure how you wanted to handle the "weekly" occurrence, so I guessed. And, please note that I'm from the US so I'm using mm/dd/yyyy format. That being said, here's what I came up with:

declare @id int
declare @recurrance varchar(10)
declare @duepattern int
declare @iterator int
declare @dateString varchar(10)
declare cursor1 cursor for
select id, recurrance, duepattern from dbo.Table_1

open cursor1
fetch next from cursor1 into @id, @recurrance, @duepattern 
while @@FETCH_STATUS = 0
begin
	if @recurrance = 'weekly'
	begin
		set @iterator = 1
		set @dateString = '01/01/' + cast(year(getdate()) as varchar(4))
		print @datestring
		insert into dbo.Table_2 (history_id, date_due)
		values (@id, @dateString)
		while @iterator < 52
		begin
			set @dateString = convert(varchar(10), DATEADD(dd, 7, cast(@datestring as datetime)), 101)
			insert into dbo.Table_2 (history_id, date_due)
			values (@id, @dateString)
			set @iterator = @iterator + 1
		end
	end	

	if @recurrance = 'monthly'
	begin
		set @iterator = 1
		while @iterator < 13
		begin
			set @dateString = ltrim(cast(@iterator as varchar(2))) + '/' + ltrim(cast(@duepattern as varchar(2))) + '/' + cast(year(getdate()) as varchar(4))
			insert into dbo.Table_2 (history_id, date_due)
			values (@id, @dateString)
			set @iterator = @iterator + 1
		end
	end	

	if @recurrance = 'yearly'
	begin
		set @iterator = 1
		while @iterator < 13
		begin
			set @dateString = '01/' + ltrim(cast(@duepattern as varchar(2))) + '/' + cast(year(getdate()) + @iterator - 1 as varchar(4))
			insert into dbo.Table_2 (history_id, date_due)
			values (@id, @dateString)
			set @iterator = @iterator + 1
		end
	end	

	fetch next from cursor1 into @id, @recurrance, @duepattern 
end

close cursor1
deallocate cursor1

select history_id, CONVERT(varchar(10), date_due, 101) as date from dbo.Table_2

Obviously, you'll have to tweak the "weekly" recurrence if you want to have it iterate for a specific day of the week.

I hope this helps!

[uva1102]

what i mean was.. Table 2 records based on table 1 ID, Recurrance and Duepattern..

table 1

ID Recurrance DuePattern
1 Monthly 25
2 Yearly 10

table 2

History_ID Date Due
1 25/1/2011
1 25/2/2011
1 25/3/2011
1
1
1
1
1
1
1 25/12/2011

2 10/1/2011
2 10/1/2012
2 10/1/2013
2 10/1/2014

[uva1102]

Hello Bitbit..

Thank you very much..
still finding ERROR..
Output was not as expected.

Msg 515, Level 16, State 2, Line 48
Cannot insert the value NULL into column 'ID', table 'system.dbo.preventiveRecord'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(5495 row(s) affected)

[Pgmer]

Is ID column is primary key or do you have constraint? Is the ID column is set to autoincrement?

[BitBlt]

How about you post the SQL that you're actually trying out when you get your error? The code I posted worked just fine for me, given the constraints and examples you originally listed.