insert statement problem

Hi all,

I'm trying to calculate the data from one table and insert the results into another table. The queries are the following:

1. select top 5 userId, count(photoId) numOfPhoto from photo group by userId order by count(photoId) desc

2. select top 5 userId, count(photoId) numOfPhoto from photo where datesubmitted > DATEADD(dd,-30,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) and datesubmitted < DATEADD(dd,-8,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))group by userId order by count(photoId) desc

3. select top 5 userId, count(photoId) numOfPhoto from photo where datesubmitted > DATEADD(dd,-7,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) and datesubmitted < DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) group by userId order by count(photoId) desc

The another table's structure is the following:

id, overall, overallNum, monthly, monthlyNum, weekly, weeklyNum

the first query's result will go to overall and overallNum, the second query's result will go to monthly and monthlyNum, the third query's result will go to weekly and weeklyNum

Sorry about my bad English. Is this possible, shall I use something like table valued function?

Thanks a lot,

Jason

I got error with following code:

Msg 170, Level 15, State 1, Procedure all_submitter, Line 10

Line 10: Incorrect syntax near '@.oNum'.

Anybody know why I'm getting that? Here is my code: thank you..

create procedure [dbo].[all_submitter]

as

set nocount on

DECLARE @.oId int, @.oNum int

declare sCursor Cursor

for select top 5 userId as oId, count(photoId) as @.oNum from photo group by userId order by count(photoId) desc

open sCursor

fetch sCursor into @.oId,

@.oNum

while (@.@.Fetch_Status = 0)

begin

insert into TopSubmitter (overallTopSubmitterId, overallTopSubmitterSubmittals) values (@.oId, @.oNum)

fetch sCursor into @.oId,

@.oNum

end

close submitterCursor

deallocate submitterCursor

return

|||

You can't do this:

count(photoId) as @.oNum

Change to

select top 5 userId as oId, count(photoId) as oNum from photo group by userId order by count(photoId) desc

and it will work. Unless I am missing something, you should be able to do:

insert into TopSubmitter (overallTopSubmitterId, overallTopSubmitterSubmittals)
select top 5 userId as oId, count(photoId) as oNum
from photo
group by userId
order by count(photoId) desc

Louis

|||thanks louis, what a stupid mistake!|||Nah, it is the kind of thing that you can't see when you are the one programming it, but when you look over someone else's work it announces itself :)