SQL Server - INSERT INTO behaviour???
Asked By Burrows
20-Nov-09 10:23 AM
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???
Msg
(1)
Entriesfrom
(1)
IDENTITY
(1)
Address1
(1)
Line1
(1)
Line2
(1)
Plamen Ratchev replied to Burrows
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 replied to Plamen Ratchev
Maybe ID column is an IDENTITY, and thus used for "versioning" the entries
from oldest to newest?
//Peter
Plamen Ratchev replied to Peso
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...
--
Plamen Ratchev
http://www.SQLStudio.com
Script to alter Identity, Identity Seed, Identity Increment SQL Server Hi all I need some Help in creating a SQL Script which Run direct on MS SQL 2000 Server. Of existing tables I want to change the: - Identity - Identity Seed - Identity Increment I already tried something with ALTER TABLE, but I guess I did something wrong Discussions ALTER TABLE (1) NOT NULL (1) CREATE TABLE (1) DROP TABLE (1) Column (1) Identity (1) Checkident (1) Create (1) Marcel 1) What do you mean "change indentity"? You can drop the column defined as an IDENTITY property 2)DBCC CHECKIDENT 3) DROP column and re-create with a new Increment You
Identity, Identity Seed, Identity Increment properties SQL Server If I set Identity property to true and set Seed to 1 and increment to 1 then SQL Server be code? Your help is great appreciated, SQL Server New Users Discussions SQL Server (1) Identity Identity Seed (1) Microsoft Access (1) Gene Wirchenko (1) Increment (1) Online (1) SQL Server will do it. Sincerely, Gene Wirchenko So Identity and seed and increment equals auto number in MS Access? Thanks agin for helping, iccsi sqlserver / cc514207.aspx SQL 2005: http: / / msdn.microsoft.com / en-us / sqlserver / bb895970.aspx keywords: Identity, Identity, Seed, , Identity, Increment, properties description: If I set Identity property to true and set
get inserted orderid in store procedure SQL Server Hi, I have an orderid field is identity(identity seed:1) , how to get the inserted value in a store procedure? Thansk. SQL Server Programming Discussions SQL Server (1) Identity (1) IDENT (1) Myid (1) Int (1) Look up SCOPE_IDENTITY IDENTITY and IDENT_CURRENT functions in SQL Server Books Online. - - Anith Lets say you inserted a new keywords: get, inserted, orderid, in, store, procedure description: Hi, I have an orderid field is identity(identity seed:1) , how to get the inserted value in a store procedure Thansk.?
Identity Column and Index SQL Server Does identity column need to be indexed? For a very large table, if we do not need the identity column to do selection, and the only purpose of the identity column is be part of the primary key (clustered), do we need an extra index for the identity column only? We are using sql server 2005. Thanks, Lijun SQL Server Discussions SQL Server (1) Clustered (1) PhD (1) BSc (1) Identity (1) Toronto (1) Enforces (1) MCDBA (1) If there is already a primary key on the identity, then you don't need another index. The PK automatically creates one. - - Tom - -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - Thomas A SQL Server MVP Toronto, ON Canada https: / / mvp.support.microsoft.com / profile / Tom.Moreau Does identity column need to be indexed? For a very large table, if we do not need