|
|
You're in the
sql server programming
group:Ordered Insert misleading syntax
sql server programming:
Hi,
I've recently read that I can't do ordered inserts, which is a shame. However the following syntax appears to be valid DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType int,EventKey varchar(50),[Date] varchar(50)) INSERT INTO @TimeLine (EventType,EventKey,[Date]) select 15 as EventType, No_ as EventKey, [Test Date] as [Date] from Test order by [Date],EventType select * from @TimeLine Although the results admittedly don't produce the desired result (ie the generated identity doesn't have the same order as the [date] and eventtype fields). My question is what effect is the order by statement having in this statement? Putting in brackets to the statement to indicate how I had previously understood this statement to be parsed, results in the error "Incorrect syntax near the keyword 'order'." DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType int,EventKey varchar(50),[Date] varchar(50)) INSERT INTO @TimeLine (EventType,EventKey,[Date]) ( select 15 as EventType, No_ as EventKey, [Test Date] as [Date] from Test order by [Date],EventType ) select * from @TimeLine Seems like to work on timeline events, I will have to use cursors, which will solve the multirow trigger problems I would encounter as well. Thanks Martin
[quoted text, click to view]
Martin wrote: > Hi, > > I've recently read that I can't do ordered inserts, which is a shame. > > However the following syntax appears to be valid > DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType int,EventKey > varchar(50),[Date] varchar(50)) > > INSERT INTO @TimeLine (EventType,EventKey,[Date]) > select 15 as EventType, No_ as EventKey, [Test Date] as [Date] > from Test > order by [Date],EventType > > select * from @TimeLine > > Although the results admittedly don't produce the desired result (ie the > generated identity doesn't have the same order as the [date] and eventtype > fields). > > My question is what effect is the order by statement having in this > statement? > > Putting in brackets to the statement to indicate how I had previously > understood this statement to be parsed, results in the error > "Incorrect syntax near the keyword 'order'." > > DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType int,EventKey > varchar(50),[Date] varchar(50)) > > INSERT INTO @TimeLine (EventType,EventKey,[Date]) > ( > select 15 as EventType, No_ as EventKey, [Test Date] as [Date] > from Test > order by [Date],EventType > ) > select * from @TimeLine > > > Seems like to work on timeline events, I will have to use cursors, which > will solve the multirow trigger problems I would encounter as well. > > Thanks > Martin You are right to call this syntax misleading. Microsoft has given confused messages in the past as to what the correct interpretation of these statements should be. ORDER BY in an INSERT... SELECT statement appears to determine the sequence of the IDENTITY values in the target table in some cases but not in others. This may be a bug. Personally I believe the best policy is to avoid using ORDER BY at all in INSERT SELECT statements, except where you need to select rows using TOP. In any case, it seems to be safest to assume that the order of IDENTITY column values is undefined and therefore unpredictable. -- David Portas SQL Server MVP --
[quoted text, click to view]
Martin wrote: > > > Seems like to work on timeline events, I will have to use cursors, which > will solve the multirow trigger problems I would encounter as well. > If you explain what you actually want to achieve then I expect someone can help you. There are other possibilities (see below). I wouldn't recommend using cursors in a multi-row trigger. In SQL Server 2000: SELECT (SELECT COUNT(*) FROM authors WHERE au_id <= A.au_id) AS row_no, au_id, au_lname, au_fname FROM authors AS A ; In SQL Server 2005: SELECT ROW_NUMBER() OVER (ORDER BY au_id) AS row_no, au_id, au_lname, au_fname FROM authors AS A ; -- David Portas SQL Server MVP --
Martin
What will be happen if you have SELECT * FROM Table ORDER BY [date] INSERT INTO with SELECT (ORDER BY) will depend on internal physical sort of the table , I mean does exist a primary key on the column? [quoted text, click to view] "Martin" <x@y.z> wrote in message news:uA%23CLe1AGHA.1216@TK2MSFTNGP14.phx.gbl... > Hi, > > I've recently read that I can't do ordered inserts, which is a shame. > > However the following syntax appears to be valid > DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType int,EventKey > varchar(50),[Date] varchar(50)) > > INSERT INTO @TimeLine (EventType,EventKey,[Date]) > select 15 as EventType, No_ as EventKey, [Test Date] as [Date] > from Test > order by [Date],EventType > > select * from @TimeLine > > Although the results admittedly don't produce the desired result (ie the > generated identity doesn't have the same order as the [date] and eventtype > fields). > > My question is what effect is the order by statement having in this > statement? > > Putting in brackets to the statement to indicate how I had previously > understood this statement to be parsed, results in the error > "Incorrect syntax near the keyword 'order'." > > DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType int,EventKey > varchar(50),[Date] varchar(50)) > > INSERT INTO @TimeLine (EventType,EventKey,[Date]) > ( > select 15 as EventType, No_ as EventKey, [Test Date] as [Date] > from Test > order by [Date],EventType > ) > select * from @TimeLine > > > Seems like to work on timeline events, I will have to use cursors, which > will solve the multirow trigger problems I would encounter as well. > > Thanks > Martin > >
Hi Uri,
The sort columns weren't part of the primary key. I've now changed the primary key accordingly. Tried doing the insert again, without specifying the sort order, but it didn't get sorted in primary key order, but when I put the order by clause back in, it *does* work as required. So I conclude it *is* possible to do ordered inserts, but only if the sort order is contained (and has the same hierarchy for multiple columns) in the primary key. Thanks alot Martin [quoted text, click to view] "Uri Dimant" <urid@iscar.co.il> wrote in message news:eixIdx6AGHA.3928@tk2msftngp13.phx.gbl... > Martin > What will be happen if you have SELECT * FROM Table ORDER BY [date] > > INSERT INTO with SELECT (ORDER BY) will depend on internal physical sort > of the table , I mean does exist a primary key on the column? > > > > > "Martin" <x@y.z> wrote in message > news:uA%23CLe1AGHA.1216@TK2MSFTNGP14.phx.gbl... >> Hi, >> >> I've recently read that I can't do ordered inserts, which is a shame. >> >> However the following syntax appears to be valid >> DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType int,EventKey >> varchar(50),[Date] varchar(50)) >> >> INSERT INTO @TimeLine (EventType,EventKey,[Date]) >> select 15 as EventType, No_ as EventKey, [Test Date] as [Date] >> from Test >> order by [Date],EventType >> >> select * from @TimeLine >> >> Although the results admittedly don't produce the desired result (ie the >> generated identity doesn't have the same order as the [date] and >> eventtype fields). >> >> My question is what effect is the order by statement having in this >> statement? >> >> Putting in brackets to the statement to indicate how I had previously >> understood this statement to be parsed, results in the error >> "Incorrect syntax near the keyword 'order'." >> >> DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType int,EventKey >> varchar(50),[Date] varchar(50)) >> >> INSERT INTO @TimeLine (EventType,EventKey,[Date]) >> ( >> select 15 as EventType, No_ as EventKey, [Test Date] as [Date] >> from Test >> order by [Date],EventType >> ) >> select * from @TimeLine >> >> >> Seems like to work on timeline events, I will have to use cursors, which >> will solve the multirow trigger problems I would encounter as well. >> >> Thanks >> Martin >> >> > >
Hi David,
Please see my reply to Uri. Appears under specific conditions ordered inserts do work (ie when the sort order is contained and compatible with the primary key of the insert table). My problem domain is processing a time series of events, and then being able to report historically at any instance in history. At least with an ordered insert, I now have the *choice* of using a cursor within the multirow trigger, or trying to do some multirow handling. Whereas before I used a cursor to force ordered single row inserts, which would not allow any multirow trigger handling. Thanks for the alternative solutions. I'm on SQL 2000, so I will keep a note of that technique in case there is a flaw in my work. I'm sure the problem domain is not at all unusual, although suprisingly involved. I have a copy of the book "Developnig Time Oriented applications in SQL", but I haven't (yet) seen a concept of a timeline table such as I am trying to use. Any hints you have in this area would be much appreciated. Thanks Martin [quoted text, click to view] "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:1134902293.995311.229300@g49g2000cwa.googlegroups.com... > Martin wrote: >> >> >> Seems like to work on timeline events, I will have to use cursors, which >> will solve the multirow trigger problems I would encounter as well. >> > > If you explain what you actually want to achieve then I expect someone > can help you. There are other possibilities (see below). I wouldn't > recommend using cursors in a multi-row trigger. > > In SQL Server 2000: > > SELECT > (SELECT COUNT(*) > FROM authors > WHERE au_id <= A.au_id) AS row_no, > au_id, au_lname, au_fname > FROM authors AS A ; > > In SQL Server 2005: > > SELECT ROW_NUMBER() OVER (ORDER BY au_id) AS row_no, > au_id, au_lname, au_fname > FROM authors AS A ; > > -- > David Portas > SQL Server MVP > -- >
The DDL for my TimeLine table is
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TimeLine]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TimeLine] GO CREATE TABLE [dbo].[TimeLine] ( [EventID] [int] IDENTITY (1, 1) NOT NULL , [Date] [datetime] NOT NULL , [EventType] [int] NOT NULL , [EventKey] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY] GO strangely it doesn't show the primary key which is now PK_TimeLine EventType Ascending Date Ascending EventKey Ascending I have a collection event type tables. I convert each individual event primary key into a string, and store this as EventKey A quick example would be as follows Event Type 2 (many on one day can occur) (Number is the Event Key) Date Number Other attributes 1/Jan/2005 001 xxx 2/Jan/2005 002 yyy 2/Jan/2005 003 zzz Event Type 1 (only one on each day) (Date is the EventKey) Date other attributes 1/Jan/2005 abc 3/Jan/2005 def TimeLine should look like this: EventID Date EventType EventKey 1 1/Jan/2005 1 1/Jan/2005 2 1/Jan/2005 2 001 3 2/Jan/2005 2 002 4 2/Jan/2005 2 003 Thanks Martin [quoted text, click to view] "Uri Dimant" <urid@iscar.co.il> wrote in message news:eUiHwf9AGHA.532@TK2MSFTNGP15.phx.gbl... > Martin > I did some testing and got that an identity column has the "same order" > as dt column . I don't understand you please provide ddl+ samle data + > expected result > > > create table #test > ( > col1 int not null identity (1,1) primary key, > col2 datetime > ) > > create table #dates > ( > dt datetime not null > ) > > > declare @d datetime > set @d = '20000101' > while @d < '20050101' begin > if datepart(dw,@d) between 2 and 6 > insert into #dates select @d > set @d = @d + 1 > end > > insert into #test (col2) select dt from #dates > > select * from #test > > > > > > "Martin" <x@y.z> wrote in message > news:uYbHbU9AGHA.3536@TK2MSFTNGP11.phx.gbl... >> Hi Uri, >> >> The sort columns weren't part of the primary key. >> >> I've now changed the primary key accordingly. >> Tried doing the insert again, without specifying the sort order, but it >> didn't get sorted in primary key order, but when I put the order by >> clause back in, it *does* work as required. >> >> So I conclude it *is* possible to do ordered inserts, but only if the >> sort order is contained (and has the same hierarchy for multiple columns) >> in the primary key. >> >> Thanks alot >> Martin >> >> "Uri Dimant" <urid@iscar.co.il> wrote in message >> news:eixIdx6AGHA.3928@tk2msftngp13.phx.gbl... >>> Martin >>> What will be happen if you have SELECT * FROM Table ORDER BY [date] >>> >>> INSERT INTO with SELECT (ORDER BY) will depend on internal physical sort >>> of the table , I mean does exist a primary key on the column? >>> >>> >>> >>> >>> "Martin" <x@y.z> wrote in message >>> news:uA%23CLe1AGHA.1216@TK2MSFTNGP14.phx.gbl... >>>> Hi, >>>> >>>> I've recently read that I can't do ordered inserts, which is a shame. >>>> >>>> However the following syntax appears to be valid >>>> DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType >>>> int,EventKey varchar(50),[Date] varchar(50)) >>>> >>>> INSERT INTO @TimeLine (EventType,EventKey,[Date]) >>>> select 15 as EventType, No_ as EventKey, [Test Date] as [Date] >>>> from Test >>>> order by [Date],EventType >>>> >>>> select * from @TimeLine >>>> >>>> Although the results admittedly don't produce the desired result (ie >>>> the generated identity doesn't have the same order as the [date] and >>>> eventtype fields). >>>> >>>> My question is what effect is the order by statement having in this >>>> statement? >>>> >>>> Putting in brackets to the statement to indicate how I had previously >>>> understood this statement to be parsed, results in the error >>>> "Incorrect syntax near the keyword 'order'." >>>> >>>> DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType >>>> int,EventKey varchar(50),[Date] varchar(50)) >>>> >>>> INSERT INTO @TimeLine (EventType,EventKey,[Date]) >>>> ( >>>> select 15 as EventType, No_ as EventKey, [Test Date] as [Date] >>>> from Test >>>> order by [Date],EventType >>>> ) >>>> select * from @TimeLine >>>> >>>> >>>> Seems like to work on timeline events, I will have to use cursors, >>>> which will solve the multirow trigger problems I would encounter as >>>> well. >>>> >>>> Thanks >>>> Martin >>>> >>>> >>> >>> >> >> > >
Martin
I did some testing and got that an identity column has the "same order" as dt column . I don't understand you please provide ddl+ samle data + expected result create table #test ( col1 int not null identity (1,1) primary key, col2 datetime ) create table #dates ( dt datetime not null ) declare @d datetime set @d = '20000101' while @d < '20050101' begin if datepart(dw,@d) between 2 and 6 insert into #dates select @d set @d = @d + 1 end insert into #test (col2) select dt from #dates select * from #test [quoted text, click to view] "Martin" <x@y.z> wrote in message news:uYbHbU9AGHA.3536@TK2MSFTNGP11.phx.gbl... > Hi Uri, > > The sort columns weren't part of the primary key. > > I've now changed the primary key accordingly. > Tried doing the insert again, without specifying the sort order, but it > didn't get sorted in primary key order, but when I put the order by clause > back in, it *does* work as required. > > So I conclude it *is* possible to do ordered inserts, but only if the sort > order is contained (and has the same hierarchy for multiple columns) in > the primary key. > > Thanks alot > Martin > > "Uri Dimant" <urid@iscar.co.il> wrote in message > news:eixIdx6AGHA.3928@tk2msftngp13.phx.gbl... >> Martin >> What will be happen if you have SELECT * FROM Table ORDER BY [date] >> >> INSERT INTO with SELECT (ORDER BY) will depend on internal physical sort >> of the table , I mean does exist a primary key on the column? >> >> >> >> >> "Martin" <x@y.z> wrote in message >> news:uA%23CLe1AGHA.1216@TK2MSFTNGP14.phx.gbl... >>> Hi, >>> >>> I've recently read that I can't do ordered inserts, which is a shame. >>> >>> However the following syntax appears to be valid >>> DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType int,EventKey >>> varchar(50),[Date] varchar(50)) >>> >>> INSERT INTO @TimeLine (EventType,EventKey,[Date]) >>> select 15 as EventType, No_ as EventKey, [Test Date] as [Date] >>> from Test >>> order by [Date],EventType >>> >>> select * from @TimeLine >>> >>> Although the results admittedly don't produce the desired result (ie the >>> generated identity doesn't have the same order as the [date] and >>> eventtype fields). >>> >>> My question is what effect is the order by statement having in this >>> statement? >>> >>> Putting in brackets to the statement to indicate how I had previously >>> understood this statement to be parsed, results in the error >>> "Incorrect syntax near the keyword 'order'." >>> >>> DECLARE @TimeLine TABLE(EventID int IDENTITY(1,1),EventType int,EventKey >>> varchar(50),[Date] varchar(50)) >>> >>> INSERT INTO @TimeLine (EventType,EventKey,[Date]) >>> ( >>> select 15 as EventType, No_ as EventKey, [Test Date] as [Date] >>> from Test >>> order by [Date],EventType >>> ) >>> select * from @TimeLine >>> >>> >>> Seems like to work on timeline events, I will have to use cursors, which >>> will solve the multirow trigger problems I would encounter as well. >>> >>> Thanks >>> Martin >>> >>> >> >> > >
1) What is the most basic property of a table? It is a set of rows and
it has no order by definition. Your request for "ordered insertion" makes no sense. Entire sets come and go in SQL as units, not like records in a sequential file. 2)>> My problem domain is processing a time series of events, and then being able to report historically at any instance in history. << You never read Dr. Codd's rules! All data in an RDBMS is represented by scalar values in the columns of tables. If you want to find the time of an event, you need to put in a timestamp in the table. [quoted text, click to view] >> I have a copy of the book "Developing Time Oriented Applications in SQL", but I haven't (yet) seen a concept of a timeline table such as I am trying to use. << Rick's book is a good reference, but it is a bit advanced if you don't know RDBMS basics. Do not get Chris Date's book at all; their data model is wrong. The important point that Newbies miss is that time is a continuum (see Zeno for philosophy and Einstein for physics). You need to model it with implicit or explicit (start, end) pairs and not single points. I also see that your DDL as well as your mental model is based on a sequential file since you have a totally non-relational IDENTITY column in it. Try something more like this: CREATE TABLE Events (event_name CHAR(15) NOT NULL PRIMARY KEY, event_type INTEGER DEFAULT 1 NOT NULL CHECK (event_type IN (1, 2, 3, ..)) , start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, end_time DATETIME, -- null means on-going CHECK (start_time < end_time), ..); You might add grandularity constraints to start_time and end_time. Now you can find overlaps, containments, preceeds, etc. as per Rick's definitions using BETWEENs and other predicates with a calendar table. Some of this is covered in SQL FOR SMARTIES. You have missed the whole point of RDBMS.
Celko,
Some further information: The events don't occur directly on the sql server, they are imported on a daily basis from another system. The events don't have an associated time with them, just a date. The relative order of events on one day is determined by the event type and other attributes specific to each event. Some events can be back dated, so I need to be able to roll back events on the time line and then roll forward again (modifying affected tables both going back and forward) If I wasn't doing an ordered insert, I would be using a cursor to insert one event at a time. Either way, I then intend using triggers to process each event sequentially (by updating other tables). So although time is a continuum, I don't think I can take advantage of that. Any further tips much appreciated. Martin [quoted text, click to view] "--CELKO--" <jcelko212@earthlink.net> wrote in message news:1135006464.613588.298670@g44g2000cwa.googlegroups.com... > 1) What is the most basic property of a table? It is a set of rows and > it has no order by definition. Your request for "ordered insertion" > makes no sense. Entire sets come and go in SQL as units, not like > records in a sequential file. > > 2)>> My problem domain is processing a time series of events, and then > being able > to report historically at any instance in history. << > > You never read Dr. Codd's rules! All data in an RDBMS is represented > by scalar values in the columns of tables. If you want to find the > time of an event, you need to put in a timestamp in the table. > >>> I have a copy of the book "Developing Time Oriented Applications in >>> SQL", but I haven't (yet) seen a concept of a timeline table such as I >>> am trying to use. << > > Rick's book is a good reference, but it is a bit advanced if you don't > know RDBMS basics. Do not get Chris Date's book at all; their data > model is wrong. > > The important point that Newbies miss is that time is a continuum (see > Zeno for philosophy and Einstein for physics). You need to model it > with implicit or explicit (start, end) pairs and not single points. I > also see that your DDL as well as your mental model is based on a > sequential file since you have a totally non-relational IDENTITY column > in it. > > Try something more like this: > > CREATE TABLE Events > (event_name CHAR(15) NOT NULL PRIMARY KEY, > event_type INTEGER DEFAULT 1 NOT NULL > CHECK (event_type IN (1, 2, 3, ..)) , > start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, > end_time DATETIME, -- null means on-going > CHECK (start_time < end_time), > ..); > > You might add grandularity constraints to start_time and end_time. > > Now you can find overlaps, containments, preceeds, etc. as per Rick's > definitions using BETWEENs and other predicates with a calendar table. > Some of this is covered in SQL FOR SMARTIES. > > > > > > > You have missed the whole point of RDBMS. >
Okay, you have rules for ordering each day's work. The convention I
know is bank deposits and withdrawls. You always post credits before debits, no matter when the actual paperwork arrived at the bank. Without more specs, my guess would be to use a VIEW that encapsulates the ordering rules, so that the data is always correct when you access it. But again, the problem is that tables have no ordering. You can put ORDER BY on your SELECTs all you want, but the SQL is free to ignore them. In fact, as the engine gets more parallelism in it, you can bet on it. It is a lot faster to load and query data in parallel. A few releases of SQL Server ago, "dialect programmers" depended on the GROUP BY clause to force a sort and they did not write an explicit ORDER BY in their code. We good SQL programmers took the extra time to write portable code. An improved optimizer trashed such code. Sometimes physical order was preserved -- especially on small test sets, so the dialect code got past people to blow up on large, produciton datasets. You are setting yourself up for the same failure.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |