Hi
I hava a following piece of code:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int)
INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
As you can see I'm trying to copy data from the source table to the
destination one, and while doing so, assign each row a subsequent
number (d) starting from 0.
I assumed, that the SELECT clause will be run for each inserted row -
however - when I run this code as the result I receive only zeros in
the d column for all rows.
I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
Is there any way to get this query running?
Thanks
Szymon
Hi
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int)
INSERT INTO #d SELECT s,(select count(*) from #s s where s.s<=#s.s) FROM
#s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
<joozeq@.gmail.com> wrote in message
news:1163408290.886457.14770@.h54g2000cwb.googlegro ups.com...
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
|||Hi,
You can try the following query:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
Without creating the #d table you can create and insert the data of #s table
in #d by the following query:
SELECT s, ROWID=IDENTITY(int,0,1) into #d from #s
Regards
Swaprakash
"joozeq@.gmail.com" wrote:
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
|||joozeq@.gmail.com wrote:
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
Make the column "d" an identity column, and let SQL assign the
incremental value:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int identity(0,1))
INSERT INTO #d SELECT s FROM #s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Why not add an IDENTITY column to table #s?
CREATE TABLE #s
( RowID int IDENTITY,
s varchar(20)
)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
<joozeq@.gmail.com> wrote in message news:1163408290.886457.14770@.h54g2000cwb.googlegro ups.com...
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
|||I can't use IDENTITY because the problem is a bit more complex that I
might have described it at the beginning of this thread. Here's a bit
less abstract story:
I have a table with about 150 000 rows, which holds geographical
coordinates of a certain GPS receiver (placed in a vehicle) in a
certain moment. The table structure is as follows:
IdCoordinates int IDENTITY PRIMARY KEY
CarId nvarchar(50)
Longitude float
Latitude float
Date datetime (indexed)
Quite often I need to derive the speed of a car throughout a day,
basing on this data. This involves finding for each row in a day the
preceeding one (to calculate the distance driven and time elapsed
between the two). The simplest way is to construct a query basing on
NOT EXISTS operator but this prooves itself to be terribly slow.
So I figured out I'd add to the table additional column, SequenceNo
int, which for a certain row would hold number of all rows gathered
from the same car with dates lesser the the row's date. This makes the
query both simple and efficient.
However - with 150 000 rows (and very soon I expect it to be over 500
000) - adding this column requires first to properly update the
SequenceNo field for all rows.
First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
MAX ... would do, but this query sets all values to 1 (providing all
the values are NULL at the beginning) - apparently some row/table
locking issue (is it?).
Then I tried to create something like Oracle's sequence that would
return next sequence number for the given car id, but this requires one
of the following:
- SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
table )
- UDF (so I can use it in the SET clause OF the UPDATE query) that can
issue DML statements (so it can remember current seqence number for a
certain car and return the next one)
- SP (they can ofcourse use DML) that can return value and be used in a
SET clause
AFAIK none of these exist in SQL Server 2000 (though there's a hack
somewhere on the web to create a SELECT trigger). So now I've ended up
with a SP that simply reads all data from the Coordinates table with a
cursor (the data being read must be ordered by date), fetches next
available SequenceNo from a temp table and inserts it to another temp
table with the SequenceNo field set properly (I know that updating the
source table would be more appropriate here but it's very slow).
It's dirty, slow and resource consuming - my best solution so far is to
use an update curosor and update the row in place with WHERE CURRENT OF
clause - but apparently SQL Server 2000 doesn't support curors with
ORDER BY and FOR UPDATE clauses put together, so this works only for
SQL Server 2005.
I'd appreciate any suggestions on how to solve this nicely.
Szymon
Arnie Rowland napisal(a):
> Why not add an IDENTITY column to table #s?
> CREATE TABLE #s
> ( RowID int IDENTITY,
> s varchar(20)
> )
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
|||Sorry - I CAN use ordered update cursors with SQL 2000 - I simply
forgot to add PK to the table and got misleading error message.
If anyone knows a better solution to the problem then the one with
update cursor I'd appreciate suggestions.
Szymon
joozeq@.gmail.com napisal(a):[vbcol=seagreen]
> I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):
|||Perhaps one of these articles may help:
Row Number (or Rank) from a SELECT Transact-SQL statement (includes Paging)
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx
http://www.projectdmx.com/tsql/ranking.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<joozeq@.gmail.com> wrote in message
news:1164136011.455451.90600@.b28g2000cwb.googlegro ups.com...
>I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):
>
