Simple Syntax Problem
-
This is embarrassing - I can't work out why this is giving me a syntax error at the last line (ORDER BY clause) on SQL Server 7.0. I would welcome any help!
CREATE TABLE #PolicyList
(PlanNo Char(12) NOT NULL,
PolicyHolder1 Integer NOT NULL,
PolicyHolder2 Integer NULL,
CorrespondenceClient Integer NULL)
ALTER TABLE #PolicyList WITH NOCHECK ADD
CONSTRAINT PK_#PolicyList PRIMARY KEY CLUSTERED (PlanNo) WITH FILLFACTOR = 100
/* Code to populate #PolicyList omitted as irrelevant */
CREATE TABLE #SingleHolders (PolicyHolder Integer NOT Null,
CorrespondenceClient Integer NOT Null)
ALTER TABLE #SingleHolders WITH NOCHECK ADD
CONSTRAINT PK_#SingleHolders PRIMARY KEY CLUSTERED (PolicyHolder, CorrespondenceClient) WITH FILLFACTOR = 100
INSERT INTO #SingleHolders
(PolicyHolder, CorrespondenceClient)
(SELECT DISTINCT PolicyHolder1, ISNull(CorrespondenceClient, 0)
FROM #PolicyList
WHERE PolicyHolder2 IS Null
ORDER BY PolicyHolder1, ISNull(CorrespondenceClient, 0))
The SELECT compiles happily but the full INSERT gives :
Server: Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'ORDER'.
-
CREATE TABLE #PolicyList
(PlanNo Char(12) NOT NULL,
PolicyHolder1 Integer NOT NULL,
PolicyHolder2 Integer NULL,
CorrespondenceClient Integer NULL)
ALTER TABLE #PolicyList WITH NOCHECK ADD
CONSTRAINT PK_#PolicyList PRIMARY KEY CLUSTERED (PlanNo) WITH FILLFACTOR = 100
/* Code to populate #PolicyList omitted as irrelevant */
CREATE TABLE #SingleHolders (PolicyHolder Integer NOT Null,
CorrespondenceClient Integer NOT Null)
ALTER TABLE #SingleHolders WITH NOCHECK ADD
CONSTRAINT PK_#SingleHolders PRIMARY KEY CLUSTERED (PolicyHolder, CorrespondenceClient) WITH FILLFACTOR = 100
INSERT INTO #SingleHolders
(PolicyHolder, CorrespondenceClient)
(
Select dtPol.PolicyHolder1, dtPol.Col2 from
(
SELECT DISTINCT TOP 100 PERCENT PolicyHolder1, ISNull(CorrespondenceClient, 0) as Col2
FROM #PolicyList
WHERE PolicyHolder2 IS Null ORDER BY PolicyHolder1, Col2
) dtPol
)
DROP TABLE #PolicyList
DROP TABLE #SingleHolders
But on a more theorical approach. A table is a set, sets have no ordering. There's no point in ordering the data into the table. You should order the data when you select from it.
-
Thanks for the instant response!
I had a clustered key on the table that I'm inserting to so as to speed later queries and included the ORDER BY so that records would be written in key order. Is that unnecessary?
I'm afraid I still don't understand what's wrong with the original syntax. The weather's lousy, the Americans are on holiday and I was up late last night - my brain doesn't want to be at work!
Many thanks for your help.
-
Don't know either why it doesn't work, it should work with top 100 percent but it obviously doesn't.
As I said the tables have no order. You need to put an index on the temp table if you want to have better performance. If you have a normal base table, then it'll be done automatially.
-
Once again, thanks very much. To echo Steve Jones, you deserve all those points.
-
When did he say that?
-
A couple of weeks ago. Perhaps I imagined it but if he didn't, he should have!
-
Maybe you should drop him a line to have a confirmation... you wouldn't want to say something on his behalf that he doesn't approve of
. - Incidentally, the original query errors because of the brackets around the insert's select statement.If you use a select to populate an insert, it will error if the select is in brackets! Cannot tell you why!
-
Now I would call that a bug
.Thanx for the info.
-
Thanks, you're right - sort of. It happens in this case but it works fine with the brackets if you take out the ORDER BY clause.
Checking BOL (which I should have done in the first place but interpreting the syntax syntax makes my brain hurt), the syntax does not include brackets so I guess that this is a feature rather than a bug!
-
The error should still be clearer than that.
-
Just SELECT, no (SELECT ... ) (a former Oracle kid?):
INSERT INTO #SingleHolders
(PolicyHolder, CorrespondenceClient)
SELECT DISTINCT PolicyHolder1, ISNull(CorrespondenceClient, 0)
FROM #PolicyList
WHERE PolicyHolder2 IS Null
ORDER BY PolicyHolder1, ISNull(CorrespondenceClient, 0)
And yes, sorting according to the physical order imposed, would avoid fragmentation and It'll be a little bit faster.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply