insert new rows from another row

Question

Post reply

insert new rows from another row

najeeb selk

SSC Enthusiast

Points: 103
More actions
March 29, 2006 at 8:06 am

#113355

Hi all
I have table that have these columns
(Name, DateFrom, DateTo)
and i want when i insert the new row to insert number of
datediff(day,DateFrom, DateTo)
in another table that have
(Name, Date)
example
when I insert this row
DateTo
DateFrom
Name
05/03/2006
02/03/2006
Najeeb
i want to insert these rows in the second table as
Date
Name
02/03/2006
Najeeb
03/03/2006
Najeeb
04/03/2006
Najeeb
05/03/2006
Najeeb
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 1

Hi,

To achieve this you have to use the trigger for insert on that table,

and in this trigger you can insert the inserted row in the second table with using of proper logic.

Regards,

Amit Gupta.

RyanRandall SSChampion Points: 13623 More actions · Answer 2

And here's the kind of logic you can use in your trigger/sp/whatever...

--data

declare @t table (Name varchar(10), DateFrom datetime, DateTo datetime)

insert @t

select 'Najeeb', '2 Mar 2006', '5 Mar 2006'

union all select 'Bob', '13 Feb 2006', '13 Feb 2006'

union all select 'Sally', '1 Jan 2006', '13 Jan 2006'

--numbers table

declare @Numbers table (i int identity(0, 1), j bit)

insert @Numbers select top 10000 null from master.dbo.syscolumns a, master.dbo.syscolumns b

--calculation

select Name, dateadd(d, i, DateFrom) as Date

from @t, @Numbers

where datediff(d, DateFrom, DateTo) >= i

order by Name, Date

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

You really do like that (very) handy Numbers table don't you...

..If you don't want to maintain a separate table and just want to "report" from existing table then you could always do something like this...there..now you have all these choices..

DECLARE @Diff Int
DECLARE @Ctr Int
SELECT @Diff = DATEDIFF(day, DateFrom, DateTo) FROM myTable WHERE Name = 'Najeeb'
SET @Ctr = 0
WHILE @Ctr <= @Diff
BEGIN
   SELECT Name, DATEADD(day, @Ctr, DateFrom) as Date FROM myTable WHERE Name = 'Najeeb'
   SET @Ctr = @Ctr + 1
END

**ASCII stupid question, get a stupid ANSI !!!**

RyanRandall SSChampion Points: 13623 More actions · Answer 4

When other solutions are loopy, you can always count on numbers

(sorry )

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

najeeb selk SSC Enthusiast Points: 103 More actions · Answer 5

najeeb selk

SSC Enthusiast

Points: 103

March 29, 2006 at 9:21 am

#629534

Thank you