xracer xracer -4 years ago 192
SQL Question

Incorrect syntax near 'order' in insert query

So... I want to be able to insert the results of a query in a table.

SELECT top 30 dt ,
count(*) AS NumberOfOrders
FROM shoptable
WHERE (name LIKE '%shop%')
GROUP by dt ORDER by dt


This query successfully returns the number of orders per each day from previous days.

Now I want to insert this into a table called shop_stats and this is my query:

INSERT INTO shop_stats
(dt, NumberOfOrders)
(SELECT top 30 dt ,
count(*) AS NumberOfOrders
FROM shoptable
WHERE (name LIKE '%shop%')
GROUP by dt ORDER by dt)


I get the following error:


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


If I remove the order by clause .... I will have 30 random days with orders.. not the last 30 as I need.

Does anybody know how to solve this in a relatively easy way? (i.e. no additional scripts that would remove the unnecessary data etc. - I just need a single SQL query).

Answer Source

There's no intrinsic ordering of rows within the table - any specific ordering is supposed to be done when you're getting the data out, not when you're putting them in.
So, just remove the order by clause, insert them, and then order them again when you select them.

That, said, if you absolutely, positively, need to insert them in a specified order (as in your case), you can use a subquery with top clause

insert into shop_stats (dt, NumberOfOrders) 
select * 
  from (select top 30 dt, count(*) as NumberOfOrders 
        from shoptable 
        where (name like '%shop%') 
        group by dt 
        order by dt)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download