How to create a copy of rows with different dates

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to create a copy of rows with different dates

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-04-15 : 19:13:51

I want to create 3 sets of same existing record with different due_dates in it.

i pass 3 as thre new rows and 30 as increment days, so want a for next loop 3 times. first time for first set of record duedate will be due_date + 30days, for second record due_date + 60 days, for third record due_date + 90

i want to use the following query, can you please help me.

for i = i +30

insert into tbl_review(reviewby,due_date)
select reviewby,due_date+30 from tbl_mytaskitems where masterid=23

next

Please help thank you very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 19:31:46

still on the same issue http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81950 ?


insert into tbl_review(reviewby, due_date)
select reviewby, due_date + (30 * n)
from   tbl_mytaskitems 
cross join (select 1 as n union all select 2 as n union all select 3 as n) n
where   masterid = 23

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-04-15 : 21:06:32

Hello KH,
At the crossjoin i am confused, the number can be 3 or 4 or 5 or 6 , it can be any number dynamic.
****************************************************************
cross join (select 1 as n union all select 2 as n union all select 3 as n) n
****************************************************************
Thank you very much for the information...

quote:
Originally posted by khtan

still on the same issue http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81950 ?

insert into tbl_review(reviewby, due_date)
select reviewby, due_date + (30 * n)
from tbl_mytaskitems
cross join (select 1 as n union all select 2 as n union all select 3 as n) n
where masterid = 23

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 21:17:56

quote:
the number can be 3 or 4 or 5 or 6 , it can be any number dynamic.

I have asked you before in the previous thread. What is the maximum possible number you are expecting ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 21:22:59

From http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81950#299179

quote:
Originally posted by khtan

what is the max number of records you will be expecting ? If it is typically small, you can just cross join to a derived table as follows. If you are looking at a large number of records, then make use of F_TABLE_NUMBER_RANGE here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

insert into tbl_expedite(due_date)
select dateadd(day, 30 * n, d.due_date)
from tbl_copyfromExpedite d
cross join
(
select n = 1 union all select n = 2 union all select n = 3 union all select n = 4 union all select n = 5
) n
where n <= 3

KH

Assuming that the maximum number that you are expecting is 5, you can use the above query. For 3 records, you just need to specify n <= 3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 21:31:32

IF for whatever reason your business rules does not implicate this or you can't decide what is the max or the max number of records is large ?

quote:
If you are looking at a large number of records, then make use of F_TABLE_NUMBER_RANGE here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 height="1" noshade id="quote">


declare @n int -- @n is the number of records that you need
insert into tbl_expedite(due_date)
select 	dateadd(day, 30 * NUMBER, d.due_date)
from 	tbl_copyfromExpedite d
        cross join F_TABLE_NUMBER_RANGE(1, @n)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 21:33:54

OR JUST USE THE WHILE LOOP


declare @i int

select @i = 1
WHILE (@i < 5) -- for 5 records
BEGIN
    insert into tbl_review(reviewby, due_date)
    select reviewby, due_date + (30 * @i)
    from   tbl_mytaskitems 
    where  masterid = 23
    
    select @i = @i + 1
END

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-04-16 : 00:16:20

Really appreciate... Thanks a Lot KH.

quote:

Originally posted by khtan

OR JUST USE THE WHILE LOOP


declare @i int

select @i = 1
WHILE (@i < 5) -- for 5 records
BEGIN
    insert into tbl_review(reviewby, due_date)
    select reviewby, due_date + (30 * @i)
    from   tbl_mytaskitems 
    where  masterid = 23
    
    select @i = @i + 1
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources