Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com
> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default...b;en-us;302621
http://support.microsoft.com/default...b;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com

Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
--
Message posted via http://www.sqlmonster.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;302621
http://support.microsoft.com/default.aspx?scid=kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via SQLMonster.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.sqlmonster.com

Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/defaul...kb;en-us;302621
http://support.microsoft.com/defaul...kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com

reg error in sql

CREATE TABLE [dbo].[ContactInfo] (
[ContactID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MiddleName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JobTitle] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OfficePhone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomePhone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mobile] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OfficialEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PersonalEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

In the above code i get the error"Line 3: Incorrect syntax near 'COLLATE'. " while compiling in sql

What is the error?

GO

i just ran the whole thing in my SQL Server (Sql Server 2000) and it worked fine.

hth,

mcm

|||

I ran this script in SQL server 2005 & it run without any problems :-) which version of SQL server are you using ?

Referencing destination table in INSERT SELECT statement

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):
>

Referencing destination table in INSERT SELECT statement

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
SzymonHi
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.googlegroups.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.googlegroups.co
m...
> 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 th
e 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/defaul...kb;en-us;186133
http://sqljunkies.com/WebLog/amacha...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.googlegroups.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):
>

Referencing destination table in INSERT SELECT statement

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
SzymonHi
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.googlegroups.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|||This is a multi-part message in MIME format.
--=_NextPart_000_00FF_01C706FC.3EEA82B0
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
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.googlegroups.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
>
--=_NextPart_000_00FF_01C706FC.3EEA82B0
Content-Type: text/html;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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
wrote in message news:1163408290.886457.14770@.h54g2000cwb.googlegroups.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>

---

--=_NextPart_000_00FF_01C706FC.3EEA82B0--|||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):
> 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
> >
> >|||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.googlegroups.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):
>> 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
>>
>

Reference Excel for date?

I have the following query;

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime1 >= CAST(CONVERT(VARCHAR(8), GETDATE() - 4, 112) AS DATETIME)) AND (DateTime2 < CAST(CONVERT(VARCHAR(8), GETDATE()-3, 112)
AS DATETIME)) AND (TagName = N'AIT-500_TOTAL_NH2CL') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)

what I would like to do is reference 2 cells in Excel with dates if I need to run this manually. How would I change the sql to do this. Thanks

DateTime1 = cell(1,1)

DateTime2 = cell(2,1)

Something like this:

Code Snippet

declare @.DateTime1 datetime,

@.DateTime2 datetime

select @.DateTime1 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A1:A1]')

select @.DateTime2 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A2:A2]')

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time

FROM v_AnalogHistory

WHERE (@.DateTime1 >= CAST(CONVERT(VARCHAR(8), GETDATE() - 4, 112) AS DATETIME))

AND (@.DateTime2 < CAST(CONVERT(VARCHAR(8), GETDATE()-3, 112) AS DATETIME))

AND (TagName = N'AIT-500_TOTAL_NH2CL') AND (wwRetrievalMode = N'delta')

AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)

ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)

You'll need to ensure that 'Ad Hoc Remote Queries' is enabled on your instance to allow OPENROWSET to function.

|||

Expanding upon Dale's suggestion, if you want to be able to run the code as it is, or on occasion, manually, then it would be better to create a Stored Procedure and call the Stored Procedure. (NOT tested and not asserted to be accurate or runnable -presented for ideas only.)

This procedure can be called in this fashion:

EXECUTE PROCEDURE dbo.MyProcedure

In this case it will attempt to find date values in the Excel File and use them.

If the cells are empty, then the procedure will use the columns DateTime1 and DateTime2.

(From the original posting, I'm assuming that there are such columns...)

EXECUTE PROCEDURE dbo.MyProcedure '2007/06/01', '2007/07/01'

In this case, the procedure will use the dates supplied.

Code Snippet

CREATE PROCEDURE dbo.MyProcedure

( @.InDate1 datetime = NULL,

@.InDate2 datetime = NULL

)

AS

BEGIN

DECLARE

@.ExDate1 datetime,

@.ExDate2 datetime

SELECT @.ExDate1 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A1:A1]')

SELECT @.ExDate2 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A2:A2]')

SELECT TOP 1

DateTime,

TagName,

[Value],

Time = convert( varchar(15), DateTime, 108 )

FROM v_AnalogHistory

WHERE ( coalesce( InDate1, nullif( ExDate1, '' ), DateTime1 ) >=

cast( convert( varchar(8), getdate() -4, 112) AS datetime )

AND coalesce( InDate2, nullif( ExDate2, '' ), DateTime2 ) <

cast( convert( varchar(8), getdate() -3, 112) AS datetime )

AND TagName = N'AIT-500_TOTAL_NH2CL'

AND wwRetrievalMode = N'delta'

AND convert( decimal(38,3), [Value] ) IS NOT NULL

)

ORDER BY

convert( decimal(38,3), [Value] ),

convert( varchar(15), DateTime, 108 )

END

GO

Reference another database without hardcoding the name of it..

Thanks Aaron
Sorry for not informing on the product, but i am working on 2000.
Some think like this could be nice:
declare @.database varchar(50)
set @.database = 'Database2'
select * from object_id(@.database).dbo.Test_Table
But your solution is like this:
declare @.database varchar(50)
declare @.sql varchar(1000)
set @.database = 'Database2'
set @.sql = 'select * from '+@.database+'.dbo.Test_Table'
executesql @.sql
Cheers
"Aaron Bertrand [SQL Server MVP]" wrote:

> In SQL Server 2005, you could create a synonym.
> In SQL Server 2000, the method I use is what you describe, get the name of
> the server and/or database and build a dynamic string. <yuck>
>
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:658A1290-95D2-40B9-A44B-4779A434F4D1@.microsoft.com...
>
>But that's not valid T-SQL syntax.
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:B281CBFB-3B65-4A75-A728-DF6C151105A4@.microsoft.com...
> Thanks Aaron
> Sorry for not informing on the product, but i am working on 2000.
> Some think like this could be nice:
> declare @.database varchar(50)
> set @.database = 'Database2'
> select * from object_id(@.database).dbo.Test_Table
> But your solution is like this:
> declare @.database varchar(50)
> declare @.sql varchar(1000)
> set @.database = 'Database2'
> set @.sql = 'select * from '+@.database+'.dbo.Test_Table'
> executesql @.sql
> Cheers
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Reducing column size

I want to reduce the length of a column in one of my database tables
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?
It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:

> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>
|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?

Reducing column size

I want to reduce the length of a column in one of my database tables
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?It basically tell you the data length in that column is more then varchar(90
0).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:

> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?

Reducing column size

I want to reduce the length of a column in one of my database tables
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?