Need help with an insert statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with an insert statement

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2010-01-14 : 18:05:41

I'm trying to get data from multiple tables and put them in a temporary table, and sort them.

Here's my query:



@SortDirection int

AS



DECLARE @Users TABLE
(
    UserID int,
    Points int
)



IF @SortDirection = 0
BEGIN
	INSERT INTO @Users (UserID, Points)
	(
		SELECT	U.UserID,
		(
			SELECT	SUM(Score)
			FROM	tblPoints P
			WHERE	P.UserID = U.UserID
		) AS Points
		FROM	tblUsers U 
		ORDER BY Points ASC   -- error here
	)
END
ELSE
BEGIN
	INSERT INTO @Users (UserID, Points)
	(
		SELECT	U.UserID,
		(
			SELECT	SUM(Score)
			FROM	tblPoints P
			WHERE	P.UserID = U.UserID
		) AS Points
		FROM	tblUsers U 
		ORDER BY Points DESC   -- error here
	)
END



SELECT *
FROM	@Users

When I try to run my stored procedure, I get two errors saying:

"Incorrect syntax near the keyword 'ORDER'."

I've put a comment where these errors occur. Does anyone know how to fix this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-14 : 18:26:48

The ORDER BYs are unnecessary in your queries since you aren't using TOP or PERCENT in them. Remove the ORDER BYs and then add the ORDER BY in when you pull the data out of the table via SELECT. The order of data is meaningless in a table, so having an ORDER BY for your INSERT is unnecessary.

To answer your question though, you need to remove the parenthesis around the outer SELECT. It should be :

INSERT INTO ...
SELECT ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-14 : 23:11:46

There's no need for an IF ELSE as well..all you need is this..

DECLARE @Users TABLE
(
    UserID int,
    Points int
)

INSERT INTO @Users (UserID, Points)
SELECT	U.UserID,SUM(P.Score) as Points
FROM tblUsers U 
inner join tblPoints P on P.UserID = U.UserID
GROUP BY U.UserID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 23:49:20

and while retrieving data using a conditional order by like below for getting your desired o/p

ORDER BY CASE WHEN @SortDirection = 0 THEN Points ELSE (-1) * Points END ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources