INSERT INTO behaviour???

[Burrows]

Hi all I was just writing a quick insert INTO and got an error
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'ORDER'.

INSERT INTO [Test].[dbo].[address1]
(
[ID] ,
[customerId] ,
[line1] ,
[line2]
)
( SELECT
[ID] ,
[customerId] ,
[line1] ,
[line2]
FROM
[dbo].[address]
ORDER BY
[customerId]
)

the funny thing is if I remove the brackets from around the select
statement it works fine any one know why this is the case???

[Plamen Ratchev]

The syntax of INSERT...SELECT is without the brackets:
http://msdn.microsoft.com/en-us/library/ms174335.aspx

Also, not sure why you have the ORDER BY, as tables are unordered sets and inserting data in order does not make sense.

--
Plamen Ratchev
http://www.SQLStudio.com

[Peso]

Maybe ID column is an IDENTITY, and thus used for "versioning" the entries
from oldest to newest?

//Peter

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:sdmdnbACAZlgLZvW...@speakeasy.net...

[Plamen Ratchev]

The ID column is included in the INSERT/SELECT list so if IDENTITY in the target table then it is forcing existing
values and ORDER BY has no effect. It will have effect if there is another column in the target table that is IDENTITY.
And sort is by customer id...