ORDER BY clause in temp table insert

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

SQL Server 2000 Forums

SQL Server Development (2000)

ORDER BY clause in temp table insert

Author

Topic

adm
Starting Member

3 Posts

Posted - 2003-04-09 : 15:40:02

Is it not possible to use an ORDER BY clause in an insert into temp table statement. The following code produces an error:

Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'order'.

create table #tmp(
datetime datetime,
ed_total float,
ed_cough float,
ed_pneumonia float,
status varchar(10))

insert into #tmp (datetime, ed_total, ed_cough, ed_pneumonia, status) (
select top 3 ed.datetime, ed.ed_total, ed.ed_cough, ed.ed_pneumonia, 'Ignore' as asdf
from tbl_ed_data ed
where (ed.hospID = 3) order by ed.edID)

select * from #tmp

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 15:46:23

I think you've got a syntax problem. You don't wrp the Select in paranthesis. Not that it matters though, but why create a label ed for the table? Anyway, Try:

create table #tmp(
datetime datetime,
ed_total float,
ed_cough float,
ed_pneumonia float,
status varchar(10))

insert into #tmp (datetime, ed_total, ed_cough, ed_pneumonia, status)
--(
select top 3 ed.datetime, ed.ed_total, ed.ed_cough, ed.ed_pneumonia, 'Ignore' as asdf
from tbl_ed_data ed
where (ed.hospID = 3) order by ed.edID
--)

select * from #tmp

Brett

8-)

adm
Starting Member

3 Posts

Posted - 2003-04-09 : 15:53:57

Yeah, that worked. I'm surprised I didn't think of that... and I spent way too much time on that. THANKS!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources