REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.codecomments.com ***
sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>
|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>
sql

REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.codecomments.com ***sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>

REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.developersdex.com ***sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>>I am facing problem while restoring database:
>> "An excepting occured while executing a Transact-SQL statement or batch.
>> Too many backup device specified for backup or restore; only 64 are
>> allowed
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> sham,
>> Since BACKUP DATABASE only supports 64 backup devices, you should not
>> need more than 64 to restore. If you paste in your RESTORE command here,
>> maybe it will be apparent what is wrong.
>> RLF
>> "sham kulkarni" <sham252664@.gmail.com> wrote in message
>> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>>I am facing problem while restoring database:
>> "An excepting occured while executing a Transact-SQL statement or batch.
>> Too many backup device specified for backup or restore; only 64 are
>> allowed
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>>
>

Referensing an alias field within the same view

I have created an Alias field in a View using a Case statement and next I
want to reference that Alias field in another Alias field with a Case
statement. I get an error stating the first field is not valid. Example of
what I'm trying to do
Column
Alias
CASE WHEN [A] = 0 AND [B] = 1 THEN 1 ELSE 0 END Expr1
CASE WHEN [Expr1] = 1 AND [C] = 1 THEN 1 ELSE 0 END Expr2
SQL doesn't like my referencing Expr1 in the second field. I suppose I could
save the first view and then create a new view based on the first but I was
hoping there might be a way to get around that. Thanks for any help.AkAlan,
As you said, it is an alias and you can not reference it in the same column
list. May be using a derived table, a view, or rewiting the expression.
select
orderid, productid, ext_price * (1.00 - (discount / 100.00)) as exp2
from
(
select orderid, productid, quantity * unitprice as ext_price
from [order details]
) as t
go
AMB
"AkAlan" wrote:

> I have created an Alias field in a View using a Case statement and next I
> want to reference that Alias field in another Alias field with a Case
> statement. I get an error stating the first field is not valid. Example of
> what I'm trying to do
> Column
> Alias
> CASE WHEN [A] = 0 AND [B] = 1 THEN 1 ELSE 0 END Expr1
> CASE WHEN [Expr1] = 1 AND [C] = 1 THEN 1 ELSE 0 END Expr2
> SQL doesn't like my referencing Expr1 in the second field. I suppose I cou
ld
> save the first view and then create a new view based on the first but I wa
s
> hoping there might be a way to get around that. Thanks for any help.
>

Referencing sql in IF THEN ELSE Statement

The Background:

From page 1 a search is created and this value is converted to a querystring and given the name "id=" and passed to page 2. Page 2 then does a lookup in the sql 2000 db useing this querystring.

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ListingDBConnectionString %>"SelectCommand="SELECT [ID], [CompName], [Package] FROM [CDetails] WHERE ([ID] = @.ID)"><SelectParameters><asp:QueryStringParameterName="ID"QueryStringField="id"Type="Int32"/></SelectParameters></asp:SqlDataSource>

The Problem:

What I then need to do is an IF THEN ELSE statement.

IF [Package in SQL1] = 4 Then

Package 4

IF [Package in SQL1] = 3 Then

Package 3

Only how do I reference the sql value in the if statement.

I have tried

<%IF Eval("Package") = 4 Then %> and with Bind - No good

Hi asiddle,

If you want to proceed IF then in SQL statement, I suggest you use CASE...WHEN...

You can first put the SelectCommand into a stored procedure, then use CASE...WHEN... to do the switch.

For more information, please check the following link:

http://msdn2.microsoft.com/en-us/library/ms181765.aspx

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Hello Kevin,

Many thanks for your reply. Unfortunately I cant make head nor tails of it. I followed your link to msdn but as usual all its actually done is confused the hell out of me. Is it possible you could drop an example?

Many thanks

|||

Hi asiddle,

I suggest you put the CASE...WHEN into a stored procedure, and take @.ID as parameter.

For example:

SELECT [ID], [CompName], [Package] FROM [CDetails] WHERE [ID] =
CASE
WHEN @.ID>1 Then XXXX
WHEN XXXX Then XXXX

|||

[Package] is an integer field ?

SELECT [ID], [CompName], 'Package ' + convert(varchar(10), [Package]) as [Package_Desc]

FROM [CDetails] WHERE ([ID] = @.ID

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

Referencing a variable set in an EXEC statement

I have code like this:
DECLARE @.CurrentValue nvarchar(1000)
EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
PRINT @.CurrentValue
When I execute this code, I get an error message that I have to declare
@.CurrentValue. Apparently the code in the EXEC statement runs in it's own
scope and it doesn't see variables outside that scope.
But if I try to declare the variable inside the EXEC string, I can't access
it outside the EXEC statement.
I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
to work either."MatthewR" <MatthewR@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||Hi,
try the following
DECLARE @.CurrentValue nvarchar(1000)
set @.CurrentValue = (Select min(Column) FROM Table)
Hope it helps
Regards
Alex|||Have a look here:
http://www.support.microsoft.com/?id=262499 Using OutPut Params &
sp_executeSql
Andrew J. Kelly SQL MVP
"MatthewR" <MatthewR@.discussions.microsoft.com> wrote in message
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||http://www.aspfaq.com/2492
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"MatthewR" <MatthewR@.discussions.microsoft.com> wrote in message
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||Look in the posting from ED and today (15.04.2005 20:47) (for Google
searcher the article
http://support.microsoft.com/defaul...kb;en-us;262499)
It describes using the Return value from an Execute. The problem is that in
the context of executing a new session is established and closed when the
statement was executed. So in the next line the value doesnt exists anymore
(in your query)
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"MatthewR" <MatthewR@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||> I tried creating a global variable (@.@.CurrentValue),
Where did you get the idea of a "global variable"? This is merely a
variable called @.CurrentValue, with a @. prefix to indicate it is a variable.
There is absolutely no difference between @.@.CurrentValue and @.CurrentValue.
A|||Thanks for the link. sp_executeSQL with OUTPUT parameters worked great!
"Andrew J. Kelly" wrote:

> Have a look here:
> http://www.support.microsoft.com/?id=262499 Using OutPut Params &
> sp_executeSql
>
> --
> Andrew J. Kelly SQL MVP
>
> "MatthewR" <MatthewR@.discussions.microsoft.com> wrote in message
> news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>
>

Referencing a value on one page of a report in a textbox

Hello,
I am creating a profit and loss statement in SQL Server reporting Services.
This single report creates a one page P&L for each loction in the company.
Some of the calculations are based on the Sales of each location (i.e.
percentages etc.). I'd like to reference the sales total for the each
location in one of the groupings of the report. The problem is, when I do
"Sum(Sales)" it doesn't sum the sales figures for the location, but only the
grouping I'm in. Is there a way to do this expression so it sums the Sales
figure for the whole location? If not, is there a way to reference a
particular textbox on the report?
Any help or direction would be greatly appreciated.
Thanks!
RickHey Rico,
Try Using:
RunningValue(Fields!Sales.Value, Sum,"LocationGroupName")
Michael C
"Rico" wrote:
> Hello,
> I am creating a profit and loss statement in SQL Server reporting Services.
> This single report creates a one page P&L for each loction in the company.
> Some of the calculations are based on the Sales of each location (i.e.
> percentages etc.). I'd like to reference the sales total for the each
> location in one of the groupings of the report. The problem is, when I do
> "Sum(Sales)" it doesn't sum the sales figures for the location, but only the
> grouping I'm in. Is there a way to do this expression so it sums the Sales
> figure for the whole location? If not, is there a way to reference a
> particular textbox on the report?
> Any help or direction would be greatly appreciated.
> Thanks!
> Rick
>
>|||Thanks!
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:1F8BB7D0-3298-4A46-BB9D-389C26386523@.microsoft.com...
> Hey Rico,
> Try Using:
> RunningValue(Fields!Sales.Value, Sum,"LocationGroupName")
> Michael C
> "Rico" wrote:
>> Hello,
>> I am creating a profit and loss statement in SQL Server reporting
>> Services.
>> This single report creates a one page P&L for each loction in the
>> company.
>> Some of the calculations are based on the Sales of each location (i.e.
>> percentages etc.). I'd like to reference the sales total for the each
>> location in one of the groupings of the report. The problem is, when I
>> do
>> "Sum(Sales)" it doesn't sum the sales figures for the location, but only
>> the
>> grouping I'm in. Is there a way to do this expression so it sums the
>> Sales
>> figure for the whole location? If not, is there a way to reference a
>> particular textbox on the report?
>> Any help or direction would be greatly appreciated.
>> Thanks!
>> Rick
>>

Reference View in SubQuery

I am wondering if it is possible to reference a view as a subquery instead
of typing the entire SELECT statement.
I have a query with many subqueries that are the same, just used in both the
FROM and WHERE clauses. If I create the subquery as a view, can I reference
the view?
WBDue to missing DDL i suggest you want to that :
Select * from Table1 T1
Left join
Viewname V1 on
T1.COlumn = V1.column
Left join
Viewname V2 on
T1.COlumn = V2.column
Where V1.Condcolumn = Somevalue AND
V2.Condcolumn = Somevalue
HTH (if not post some ddl), Jens Suessmeyer.
http://www.sqlserver2005.de
--
"WB" <none> schrieb im Newsbeitrag
news:uSZy6cNUFHA.2616@.TK2MSFTNGP14.phx.gbl...
>I am wondering if it is possible to reference a view as a subquery instead
> of typing the entire SELECT statement.
> I have a query with many subqueries that are the same, just used in both
> the
> FROM and WHERE clauses. If I create the subquery as a view, can I
> reference
> the view?
> WB
>|||What is DDL?
thank you for verifying I could use the view method. Makes my syntax easier
to read and fewer lines.
WB
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfSOimNUFHA.1432@.TK2MSFTNGP09.phx.gbl...
> Due to missing DDL i suggest you want to that :
>
> Select * from Table1 T1
> Left join
> Viewname V1 on
> T1.COlumn = V1.column
> Left join
> Viewname V2 on
> T1.COlumn = V2.column
> Where V1.Condcolumn = Somevalue AND
> V2.Condcolumn = Somevalue
> HTH (if not post some ddl), Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "WB" <none> schrieb im Newsbeitrag
> news:uSZy6cNUFHA.2616@.TK2MSFTNGP14.phx.gbl...
instead
>|||Hi From wwwcoder:
DDL.
DDL stands for Data Definition Language. These are the set of commands that
are used to define the shape of data, and how the data is stored in the
database. These commands also define the structure of database and create
the database objects.
DML
The abbreviated form of Data Modification Language, DML, is used to
define/manipulate the actual data that is stored in the database. This
includes, inserting new data and updating stored data.
DCL
DCL or the Data Control Language, provides you commands by which you can
control access to your data. This includes creating users and granting and
revoking permissions to/from them.
http://www.wwwcoder.com/main/parent...68/default.aspx
HTH, Jens Suessmeyer.
http://www.sqslerver2005.de
"WB" <none> schrieb im Newsbeitrag
news:uiOpRsNUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> What is DDL?
> thank you for verifying I could use the view method. Makes my syntax
> easier
> to read and fewer lines.
> WB
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:OfSOimNUFHA.1432@.TK2MSFTNGP09.phx.gbl...
> instead
>|||>> If I create the subquery as a view, can I reference the view? <<
Yes, as long as it is un-correlated.

Reference an alias field name in an SQL Statement

Is there a way to Reference an alias field name in an SQL Statement?
Example:
Select
1 + 1 AS F1,
F1 + 1 AS F2Hello Kent,
Darren has given a good answer to your problem. I use derived tables or
sub queries to handle the kind of functionality you are looking for.
Your example shows two columns one contains a table field with a
calculation. The second has another calculation based on the first
field. Using derived tables is a very effect way to handle complex set
operations and calculations. Check out the script below.
CREATE TABLE [MyTable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[F1] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO [sandpit].[dbo].[MyTable]( [F1])
VALUES( 100)
GO
SELECT T.F1, T.F1+1 AS F2 FROM (SELECT ID, F1+1 AS F1 FROM MyTable) AS
T
GO
DROP TABLE MyTable
GO
I hope this makes it a bit clearer. I might help if you give the group
a bit more background on what you are trying to do.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
Kent Prokopy wrote:
> Is there a way to Reference an alias field name in an SQL Statement?
> Example:
> Select
> 1 + 1 AS F1,
> F1 + 1 AS F2|||Hello Kent,
Darren has given a good answer to your problem. I use derived tables or
sub queries to handle the kind of functionality you are looking for.
Your example shows two columns one contains a table field with a
calculation. The second has another calculation based on the first
field. Using derived tables is a very effect way to handle complex set
operations and calculations. Check out the script below.
CREATE TABLE [MyTable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[F1] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO [sandpit].[dbo].[MyTable]( [F1])
VALUES( 100)
GO
SELECT T.F1, T.F1+1 AS F2 FROM (SELECT ID, F1+1 AS F1 FROM MyTable) AS
T
GO
DROP TABLE MyTable
GO
I hope this makes it a bit clearer. I might help if you give the group
a bit more background on what you are trying to do.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
Kent Prokopy wrote:
> Is there a way to Reference an alias field name in an SQL Statement?
> Example:
> Select
> 1 + 1 AS F1,
> F1 + 1 AS F2

Reference 2 Databases in SQL Statement

I need to join a table with another table in a different database. Is this possible?

Assume Table1 is in DB1 and Table2 is in DB2.

Thanks!

Brian

Simple, just prefrace the table name with the database name.

e.g. SELECT * FROM database1..table1 JOIN database2..table2 ON ...

|||

Brian:

It should simply be something like:

Code Snippet

select <columnList>

from Db1.schema1.Table1

join Db2.schema2.Table2

...

|||

search for "linked servers"

Reference 2 Databases in SQL Statement

I need to join a table with another table in a different database. Is this possible?

Assume Table1 is in DB1 and Table2 is in DB2.

Thanks!

Brian

Simple, just prefrace the table name with the database name.

e.g. SELECT * FROM database1..table1 JOIN database2..table2 ON ...

|||

Brian:

It should simply be something like:

Code Snippet

select <columnList>

from Db1.schema1.Table1

join Db2.schema2.Table2

...

|||

search for "linked servers"

Refer to webform fields in Insert statement

Can someone help with this? Let me know if what I'm trying to do is possible...

Here's code example:

@.ClientID int,

@.QuoteID int,

@.Base real,

@.One real,

@.DwellingLimit real

AS

BEGIN

Insert Into tblOne(ClientID,QuoteID,GuideID,GuideRate,GuideMult,Premium)

Select @.ClientID, @.QuoteID, GuideID,GuideRate, @.+"GuideMult"+,GuideRate*GuideMult

From tblTwo

Where Choose = 'True';

END

I need the value stored in tblTwo.GuideMult (ie. One, BaseRate) to be translated

into the numerical value shown on a webform (ie. @.One, @.BaseRate) and then

insert the numerical values into tblOne.GuideMult

Clear as mud? Does somebody have a better way to do this?

You're right -not very clear.

Please post the table DDL, and some sample data in the form of INSERT statements (see this link), and a step by stop expanation of your starting and ending results.