Incorrect syntax near the keyword 'ORDER'.

[blindsey]

I'm getting this error message:

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

For this statement:

INSERT INTO TEMP_BOOKING2 (PRISONER_ID,BOOKING_NO,
SENT_EFF_DATE1,SYSID)
(SELECT PRISONER_ID,BOOKING_NO,SENT_EFF_DATE,ROWNO
FROM TEMP_BOOKING
ORDER BY PRISONER_ID,SENT_EFF_DATE);

(It works when I take out the ORDER BY clause, but I need that for an
identity column on the target table.)

[Jesse Hersch]

if you want to explicity specify the values for an identity column, you have
to do this:

set identity_insert MyTable on
-- do the insert
set identity_insert MyTable off

see BOL for details on identity_insert

--
http://elsasoft.org

"blindsey" <blin...@dsicdi.com> wrote in message
news:104c804e-d87a-47ae...@u69g2000hse.googlegroups.com...

[Tom Cooper]

Remove the parenthesis around the select clause.

INSERT INTO TEMP_BOOKING2 (PRISONER_ID,BOOKING_NO,
SENT_EFF_DATE1,SYSID)

SELECT PRISONER_ID,BOOKING_NO,SENT_EFF_DATE,ROWNO
FROM TEMP_BOOKING
ORDER BY PRISONER_ID,SENT_EFF_DATE;

Tom

"blindsey" <blin...@dsicdi.com> wrote in message
news:104c804e-d87a-47ae...@u69g2000hse.googlegroups.com...

[David Portas]

"blindsey" <blin...@dsicdi.com> wrote in message
news:104c804e-d87a-47ae...@u69g2000hse.googlegroups.com...

Remove the second set of parentheses.

Don't use ORDER BY as part of an INSERT statement. It achieves nothing
useful because tables are always unordered.

--
David Portas