Multiple Insert Question
-
Hi all,
I have two tables:
Mice (MiceID-int,pk, BeginningDate-datetime, EndDate-datetime)
Observation (ObId-int-pk, MiceID-int, WeekNum-int, ObNotes-varchar)
What i want to do is when a new record is inserted in table Mice, calculate the number of weeks between BeginningDate and EndDate, then insert the recently created MiceID into table Observation as many times as there are weeks between BeginningDate and EndDate, and increment the WeekNum accordingly, beginning from 1.
I could get the number of weeks in a sproc but I dont know how to do multiple insert and increment. Is this possible?
Thank you for any help/advice.
-
Hi Afendi,
Sounds like you're going to need a tally/numbers table. Here's an illustration of the technique...
You may need to use a trigger to do this when a new record is inserted into the Mice table, but be careful with what you decide to do for updates.
--data
declare @Observation table (ObId int identity(1, 1), MiceID int, WeekNum int, ObNotes varchar)
--inputs
declare @MiceID int, @BeginningDate datetime, @EndDate datetime
select @MiceID = 1, @BeginningDate = '20050524', @EndDate = '20060524'
--calculation
declare @rowcount int
set @rowcount = datediff(wk, @BeginningDate, @EndDate)
declare @numbers table (i int identity(1, 1), x bit)
set rowcount @rowcount
insert @numbers select null from master.dbo.syscolumns a, master.dbo.syscolumns b
set rowcount 0
insert @Observation (MiceID, WeekNum)
select @MiceID, i from @numbers
select * from @Observation
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Dear Ryan,
I got it to work. Thank you very,very much for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply