SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to create a copy of rows with different dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

542 Posts

Posted - 04/15/2007 :  19:13:51  Show Profile  Reply with Quote
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)

Singapore
17682 Posts

Posted - 04/15/2007 :  19:31:46  Show Profile  Reply with Quote
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

Go to Top of Page

cplusplus
Aged Yak Warrior

542 Posts

Posted - 04/15/2007 :  21:06:32  Show Profile  Reply with Quote
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



Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 04/15/2007 :  21:17:56  Show Profile  Reply with Quote
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 ?


KH


Edited by - khtan on 04/15/2007 21:23:47
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 04/15/2007 :  21:22:59  Show Profile  Reply with Quote
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


KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 04/15/2007 :  21:31:32  Show Profile  Reply with Quote
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


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)    



KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 04/15/2007 :  21:33:54  Show Profile  Reply with Quote
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



KH

Go to Top of Page

cplusplus
Aged Yak Warrior

542 Posts

Posted - 04/16/2007 :  00:16:20  Show Profile  Reply with Quote
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



KH



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000