Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> SQL Server >> Transact-SQL

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

[1230 byte] By [JasonH121] at [2007-12-23]

«« iif inside iif
»» Supported MNG File Editor?

# 1

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

JasonH121 at 2007-8-30 >

top of Msdn Tech,SQL Server,Transact-SQL...

# 2

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

LouisDavidson at 2007-8-30 >

top of Msdn Tech,SQL Server,Transact-SQL...

# 3

thanks louis, what a stupid mistake!

JasonH121 at 2007-8-30 >

top of Msdn Tech,SQL Server,Transact-SQL...

# 4

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 :)

LouisDavidson at 2007-8-30 >

top of Msdn Tech,SQL Server,Transact-SQL...

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified