Is this possible......??

Say I have a table with one field and there are 26 records in it... A, B, C, D, E, ... etc.

If I do a select statement on that table I get 26 records, one for each field. (select fieldName from tblName)

Is it possible to set a variable equal to a single string off of the select statement and delimit it with a chosen delimiter (ie "A,B,C,D,E,F......")

Thank You for the help!!!With which DBMS? There is no standard SQL answer to this.|||Using MS SQL 7|||Looking for something like

set @.stringName = (select fieldName & ',' from tblName)

So that @.stringName is set to a string 'A,B,C,D,E,....'|||What about this?

drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str|||Originally posted by snail
What about this?

drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset

set @.str = @.str + (select fieldName from tblName)

something like that where my recordset can be turned into a string.|||Originally posted by gman_gsxr750
That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset

set @.str = @.str + (select fieldName from tblName)

something like that where my recordset can be turned into a string.

Just try and you'll see...|||Originally posted by snail
Just try and you'll see... Holy moley!!! I've never seen that before!!!

Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

One last question (only because I've never used the code in that way before...

can I put a conditional on it

set @.str = @.str + code from table (where id < 100)

or something like that?

And did I mention..... Thank you!|||Originally posted by gman_gsxr750
Holy moley!!! I've never seen that before!!!

Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

One last question (only because I've never used the code in that way before...

can I put a conditional on it

set @.str = @.str + code from table (where id < 100)

or something like that?

And did I mention..... Thank you!

Why not?|||Originally posted by snail
Why not? Ever get that rush when something finally goes your way and things work out?

Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.

Do you know what that kind of query is called so I can reference?|||Originally posted by gman_gsxr750
Ever get that rush when something finally goes your way and things work out?

Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.

Do you know what that kind of query is called so I can reference?

I have no idea...|||Originally posted by snail
I have no idea... OK, last question, hopefully you can help me with this.

Here's my code:
declare @.idpeople int
set @.idpeople = 200002
declare @.str varchar(8000)
set @.str = ''

select @.str = @.str + ',' + ideventcode from tblPeopleEvents where idpeople = @.idpeople

let's say this returns the following ',CXL,AS' (two codes CXL and AS)

this works fine, but if I add an order by clause to it (so it returns AS,CXL instead), I only get one of the two values (CXL)

any ideas on how far I can take the select portion (where, order by, group by, etc)?|||it's called a magic query

it works, and it produces the result by magic

hey snail, where's the comma between values?

;)|||OK, here's my final code. I used a subquery to get the result set the way I needed it. Much thanks to Snail for the help. And to r937 for the sarcasm ;).

create procedure spGetEventString
@.idpeople int,
@.eventString varchar(255) OUTPUT

as

set @.eventString = ''

-- Create string of Event codes
-- use sub query to order result set
select @.eventString = @.eventString + rTPE.ideventcode
from
(
select top 100 idEventCode + ',' as idEventCode
from tblPeopleEvents
where idpeople = @.idpeople
order by idEventCode
) as rTPE

--Remove Trailing Comma
set @.eventString = left(@.eventString,len(@.eventString)-1)

return|||Originally posted by r937
it's called a magic query

it works, and it produces the result by magic

hey snail, where's the comma between values?

;)

I am not a magician I am only learning... ;)|||I think it should be called the Loophole query, because it doesn't look like it should work, but it does.|||Originally posted by blindman
I think it should be called the Loophole query, because it doesn't look like it should work, but it does.

That is a bit harsh ...

http://www.dbforums.com/showthread.php?threadid=979593|||Originally posted by Enigma
That is a bit harsh ...

http://www.dbforums.com/showthread.php?threadid=979593 Yep, that's it. Works like a charm too!|||This type of query is the coolest thing I've learned from DBForums, but I haven't seen any Microsoft Documentation that talks about it. That's why it seems like a loophole to me.

Does anybody know of any BOL or MS Support references regarding this self-referential query?|||Thats what it should be called -->"A self-referential query"|||and it even does what Enigma was asking in the referenced post:

select @.str=@.str+case @.str when '' then '' else ',' end + code from test

Is this possible with DTS?

I would like to transfer data from one sql server to another based on
what records exist on server.
One server would be a local server (server1), the other one remote
(server2).
Is it possible to do something like this :-
select *
from server1.database1.table
where customerid not in
(select cusid from server2.database1.table)
Is this possible using DTS? If so can someone point me the right
directection.
TIA
Simon.Possible solutions:
1.Linked server ( you can use that syntax)
2.Bulk insert( just for inserting)
3. Openquery, opendatasource,openrowset (for distributed databases)(pass
through - for adohoc)
4.dts( gives you full control for permenant use)
see BOL for this
r.d
"bozzzza@.lycos.co.uk" wrote:

> I would like to transfer data from one sql server to another based on
> what records exist on server.
> One server would be a local server (server1), the other one remote
> (server2).
> Is it possible to do something like this :-
> select *
> from server1.database1.table
> where customerid not in
> (select cusid from server2.database1.table)
> Is this possible using DTS? If so can someone point me the right
> directection.
> TIA
> Simon.
>|||Hi,
This can be done if you have Linked server created from Server1 pinting to
Server2. But if you have Linked server then
you could directly use the INSERT statement rather than DTS.
Some thing like below:-
INSERT INTO TABLE select * from server1.database1.table where customerid not
in (select cusid from server2.database1.table)
Thanks
Hari
SQL Server MVP
<bozzzza@.lycos.co.uk> wrote in message
news:1123835051.556270.44930@.g14g2000cwa.googlegroups.com...
>I would like to transfer data from one sql server to another based on
> what records exist on server.
> One server would be a local server (server1), the other one remote
> (server2).
> Is it possible to do something like this :-
> select *
> from server1.database1.table
> where customerid not in
> (select cusid from server2.database1.table)
> Is this possible using DTS? If so can someone point me the right
> directection.
> TIA
> Simon.
>|||bozzza,
You can build a stored procedure which creates a link, do the transference
and at the end, delete the link.
"Hari Pra" wrote:

> Hi,
> This can be done if you have Linked server created from Server1 pinting to
> Server2. But if you have Linked server then
> you could directly use the INSERT statement rather than DTS.
> Some thing like below:-
> INSERT INTO TABLE select * from server1.database1.table where customerid n
ot
> in (select cusid from server2.database1.table)
> Thanks
> Hari
> SQL Server MVP
>
> <bozzzza@.lycos.co.uk> wrote in message
> news:1123835051.556270.44930@.g14g2000cwa.googlegroups.com...
>
>

Is this possible in SQL Server 2000 and MySQL?

hi guys, is it possible that i can retrieve records from different database like SQL Server 2000 and MySQL?
example, assuming i have some table in SQL Server 2000 and i also have in MySQL. the table in SQL Server 2000 is for example Table1 and in MySQL is Table2.
if i create a query that will combine and retrieve the fields of the two database, is it possible?
if so anyone who have idea with that and how to do that?

Thank You.

Quote:

Originally Posted by klaydze

hi guys, is it possible that i can retrieve records from different database like SQL Server 2000 and MySQL?
example, assuming i have some table in SQL Server 2000 and i also have in MySQL. the table in SQL Server 2000 is for example Table1 and in MySQL is Table2.
if i create a query that will combine and retrieve the fields of the two database, is it possible?
if so anyone who have idea with that and how to do that?

Thank You.

I assume you mean retrieving records from different servers because from different databases on the same Server it is just as simple as mentioning database name and owner in front of your table name like this:
Databasename.dbo.tablename
First ask your DBA to link both servers. With linked server it is really easy to do. If you dont have DBA go to help then search for subject linked servers. When servers are linked you query tables like this
Servername.Databasename.dbo.tablename
Also you can try OPENDATASOURCE query. Search for it in SQL help. You still need correct drivers to be installed on SQL server side and use them as provider otherwise it will not work.
Good Luck.

is this possible

hey all,
what's the best way to get all the records in a master table and a sum of a
column in a related details table?
thanks,
rodcharThe best way is to post DDL and sample data (
http://www.aspfaq.com/etiquette.asp?id=5006 )
But...
SELECT Master.PKCol, SUM(Detail.SomeCol) SumSomeCol
FROM Master
JOIN Detail ON Master.PKCol = Detail.PKCol
GROUP BY Master.PKCol
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:D309581C-EBC3-4AA9-A5BC-91AEA5FE88FF@.microsoft.com...
> hey all,
> what's the best way to get all the records in a master table and a sum of
a
> column in a related details table?
> thanks,
> rodchar|||without seeing the ddl, this is a guess.
select m.id,sum(c.col)
from master m left join child c on m.id=c.fk
group by m.id
-oj
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:D309581C-EBC3-4AA9-A5BC-91AEA5FE88FF@.microsoft.com...
> hey all,
> what's the best way to get all the records in a master table and a sum of
> a
> column in a related details table?
> thanks,
> rodchar|||Well...you query it for the things you need and sum the column that gives yo
u
the answer. Once you have it, it will be obvious how it should be assemble t
o
get those things you need.
Vagueness begets vague answers. Post DDL
Thomas
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:D309581C-EBC3-4AA9-A5BC-91AEA5FE88FF@.microsoft.com...
> hey all,
> what's the best way to get all the records in a master table and a sum of
a
> column in a related details table?
> thanks,
> rodchar|||SELECT <col_list_from_master_table>,
(SELECT SUM(<col_name> )
FROM details_table AS D
WHERE D.referencing_col = M.referenced_col) AS sumdetail
FROM master_table AS M
BG, SQL Server MVP
www.SolidQualityLearning.com
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:D309581C-EBC3-4AA9-A5BC-91AEA5FE88FF@.microsoft.com...
> hey all,
> what's the best way to get all the records in a master table and a sum of
> a
> column in a related details table?
> thanks,
> rodchar

Is this a permissions problem

I have a stored procedure that creates a temporary table, populates it,
deletes certain records from it and then selects all the data from it.
In query analyzer I get a resultset, however in my VB6 code it doesn't
return a resultset, the recordset isn't even open after running it.
If I run other stored procedures they work no problem and return a resultset
i cant seem to figure out the issue and I am assuming that its down to
permissions, as the only difference in the sp's are that this one uses temp
tables, although I thought that any #tables created inside of a stored
procedure are there till execution of the sp ends. Do I have to set
something that allows temporary tables to be created
TIAI dont think this is a permission problem. Just check to which SP u are
referring to and in which database does the SP reside.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"steven scaife" wrote:

> I have a stored procedure that creates a temporary table, populates it,
> deletes certain records from it and then selects all the data from it.
> In query analyzer I get a resultset, however in my VB6 code it doesn't
> return a resultset, the recordset isn't even open after running it.
> If I run other stored procedures they work no problem and return a results
et
> i cant seem to figure out the issue and I am assuming that its down to
> permissions, as the only difference in the sp's are that this one uses tem
p
> tables, although I thought that any #tables created inside of a stored
> procedure are there till execution of the sp ends. Do I have to set
> something that allows temporary tables to be created
> TIA|||I'm calling the right sp as I can call some others from the same database, I
can pass paramaters to the other sps and they run fine, the only difference
is one creates and uses a temp table, yet it runs fine under QA.
Its just bugging me as I can't seem to figure out the problem
"Chandra" wrote:
> I dont think this is a permission problem. Just check to which SP u are
> referring to and in which database does the SP reside.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "steven scaife" wrote:
>|||I found the solution needed to have
set nocount on in my sp
"steven scaife" wrote:

> I have a stored procedure that creates a temporary table, populates it,
> deletes certain records from it and then selects all the data from it.
> In query analyzer I get a resultset, however in my VB6 code it doesn't
> return a resultset, the recordset isn't even open after running it.
> If I run other stored procedures they work no problem and return a results
et
> i cant seem to figure out the issue and I am assuming that its down to
> permissions, as the only difference in the sp's are that this one uses tem
p
> tables, although I thought that any #tables created inside of a stored
> procedure are there till execution of the sp ends. Do I have to set
> something that allows temporary tables to be created
> TIA|||SET NOCOUNT ON suppresses DONE_IN_PROC messages and can improve performance
by avoiding extra round trips. With the default SET NOCOUNT OFF,
DONE_IN_PROC messages are returned to ADO apps as empty closed recordsets
and can interfere with data retrieval unless you skip them using the
NextRecordset method.
Hope this helps.
Dan Guzman
SQL Server MVP
"steven scaife" <stevenscaife@.discussions.microsoft.com> wrote in message
news:88F9E00F-DFB3-4E44-AAC6-454042C7DE46@.microsoft.com...
>I found the solution needed to have
> set nocount on in my sp
> "steven scaife" wrote:
>sql

Is there Limitations to Importing data to SQL Server Express

I have software that uses SQL Server Express as it's database. I am only able to import so many records until it stops and fails to allow me to import anymore.

I'm very new at this but, is there some type of limitation on Table size that's preventing me from importing anymore data?

I'd really appreciate it if someone could help me,

Thanks... Bill

Hi Bill,

SOL Server express is an evaluation copy. You need the full version. Please see this thread.........

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=157773&SiteID=1

|||

That is NOT correct -SQL Server Express is not JUST "an evaluation copy" SQL Server Express is a full featured product, albeit without some of the features included with the paid versions.

SQL Server Express is limited to a database size of 4GB; it could hold one table that held almost 4GB of data.

You may find importing data to be a bit easier using the DTSWizard. You 'should' be able to find a copy here: (assumes your SQL Server installation used [ Drive C: ].

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe

|||

Hi Arnie,

Thanks for updating that. I try, but I don't always have the correct answer.

|||

though it is a full featured free downloadable edition of sql server 2005, SQL Server express has few limitations. It can only have datbases of maximum 4 GB size. See whether u r database is exceeding this limit of not

Refer : http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx

Madhu

|||

djartsinc,

Don't take it personally, one way you keep learning is to take the risk of exposing what you don't know. I learn something here almost every day. Some of my 'teachers' are gentle, some are a bit 'rough' -but my learning keeps on...

|||

djartsinc,

Thanks for responding. I know Express is a limited version, but I thought I could use it with the sofware I'm using it with. I can't afford $4,000 dollars. The software that is using Express for a database only cost $900.00. Do you see what I mean?

Thanks,

Bill

|||

Arnie,

Thanks... I'll look for the Wizard tonight. I've been importing records via the import feature of the software I'm using. I imported 265,000 records successfully, then after that it failed to import anymore. The records are.... FirstName, LastName, Address. and I have more to go.... the problem is ... I know the name of the database, but I'm not sure I would know the table to import the records into. I'm pretty new at this.

Thanks,

Bill

|||

Hi Arnie,

No offence taken. Learning is good. I learn something new everyday

|||

Hi Bill,

I understand, and your welcome.

Is there equivalent to Sybase Dynamic Archiving for SQL Server

Sybase has a product called Dynamic Archiving which will automatically move
old records to a archive database, but all applications will see the two
database as one (no changes to source code etc.)
Is there an equivalent for SQL Server (either now, or part of 2005, or third
party) ?
Any help would be appreciated...
Regards,
Mark Donoghue
MDonoghue@.refco.comI'm not aware of anything identical.
In SQL2K... you might be able to achieve a similiar effect by looking at
Partioned Views. Not real archiving, but you might get some of the benefits
you're looking for. Stricktly a roll your own solution.
SQL2005 adds support for partioned ranges (using multiple tables) that will
make it much easier to manage this. But still roll your own.
--
"Mark Donoghue" <MarkDonoghue@.discussions.microsoft.com> wrote in message
news:A27078A5-CD17-46C1-8DD1-AA9DA6002730@.microsoft.com...
> Sybase has a product called Dynamic Archiving which will automatically
move
> old records to a archive database, but all applications will see the two
> database as one (no changes to source code etc.)
> Is there an equivalent for SQL Server (either now, or part of 2005, or
third
> party) ?
> Any help would be appreciated...
> Regards,
> Mark Donoghue
> MDonoghue@.refco.com

Is there equivalent to Sybase Dynamic Archiving for SQL Server

Sybase has a product called Dynamic Archiving which will automatically move
old records to a archive database, but all applications will see the two
database as one (no changes to source code etc.)
Is there an equivalent for SQL Server (either now, or part of 2005, or third
party) ?
Any help would be appreciated...
Regards,
Mark Donoghue
MDonoghue@.refco.com
I'm not aware of anything identical.
In SQL2K... you might be able to achieve a similiar effect by looking at
Partioned Views. Not real archiving, but you might get some of the benefits
you're looking for. Stricktly a roll your own solution.
SQL2005 adds support for partioned ranges (using multiple tables) that will
make it much easier to manage this. But still roll your own.
"Mark Donoghue" <MarkDonoghue@.discussions.microsoft.com> wrote in message
news:A27078A5-CD17-46C1-8DD1-AA9DA6002730@.microsoft.com...
> Sybase has a product called Dynamic Archiving which will automatically
move
> old records to a archive database, but all applications will see the two
> database as one (no changes to source code etc.)
> Is there an equivalent for SQL Server (either now, or part of 2005, or
third
> party) ?
> Any help would be appreciated...
> Regards,
> Mark Donoghue
> MDonoghue@.refco.com

Is there equivalent to Sybase Dynamic Archiving for SQL Server

Sybase has a product called Dynamic Archiving which will automatically move
old records to a archive database, but all applications will see the two
database as one (no changes to source code etc.)
Is there an equivalent for SQL Server (either now, or part of 2005, or third
party) ?
Any help would be appreciated...
Regards,
Mark Donoghue
MDonoghue@.refco.comI'm not aware of anything identical.
In SQL2K... you might be able to achieve a similiar effect by looking at
Partioned Views. Not real archiving, but you might get some of the benefits
you're looking for. Stricktly a roll your own solution.
SQL2005 adds support for partioned ranges (using multiple tables) that will
make it much easier to manage this. But still roll your own.
"Mark Donoghue" <MarkDonoghue@.discussions.microsoft.com> wrote in message
news:A27078A5-CD17-46C1-8DD1-AA9DA6002730@.microsoft.com...
> Sybase has a product called Dynamic Archiving which will automatically
move
> old records to a archive database, but all applications will see the two
> database as one (no changes to source code etc.)
> Is there an equivalent for SQL Server (either now, or part of 2005, or
third
> party) ?
> Any help would be appreciated...
> Regards,
> Mark Donoghue
> MDonoghue@.refco.com

Is there any way in a sproc to LOOP thru the records of a table ?

Hi. It seems to be very simple, actually, but I don't know if it is
feasible in TSQL. I have a sproc which gathers in one place many calls
to different other sprocs, all of them taking a 'StoreGroupe'
parameter. I would like to add a case where if the call has NO
StoreGroupe parameter, the sproc should LOOP thru all records in table
StoreGroupeTable, read the column StoreCode, and pass that value as a
param to the other sprocs, as in:

CREATE PROCEDURE MySproc

(
@.StoreGroupe nvarchar(6) = NULL
)

AS
if (@.StoreGroupe is not null)
Begin
Exec _Sproc1 @.StoreGroupe
Exec _Sproc2 @.StoreGroupe
Exec _Sproc3 @.StoreGroupe
Exec _Sproc4 @.StoreGroupe
............
End
Else
Begin
A 'Group Code' has NOT been specified
I want to take all the StoreGroups in table
StoreGroupeTable, in turn.
I would like to do SOMETHING LIKE THIS:
Do While not [StoreGroupeTable].EOF
Read [Code] from [StoreGroupeTable]
Set @.StoreGroupe = The value I just read
Exec _Sproc1 @.StoreGroupe
Exec _Sproc2 @.StoreGroupe
Exec _Sproc3 @.StoreGroupe
Exec _Sproc4 @.StoreGroupe
............
Loop
End
GO

Is that feasible in a sproc, or do I have to do this in the client
(ADO) ?

Thanks a lot.
Alex.You can do this using a cursor - executing a stored proc repeatedly for
each value in a column is one of the (few) cases where they're useful.
In performance terms it would probably be better to rewrite proc1,
proc2 etc. to operate on a set of values using set-based code (but that
may not be possible, of course, depending on what the procs are doing).

declare @.StoreGroupe int

declare cur cursor local static
for select SomeCode from dbo.StoreGroupes

open cur
fetch next from cur into @.StoreGroupe
while @.@.fetch_status = 0
begin
exec dbo.proc1 @.StoreGroupe
exec dbo.proc2 @.StoreGroupe
-- etc.
fetch next from cur into @.StoreGroupe
end

close cur
deallocate cur

Simon|||Thanks a lot, Simon, I'll implement this right now.

Thanks again ! :-))))

Alex.|||You have not learned to think in SQL yet and are still writing 3GL
procedural code. Let's get back to the basics of an RDBMS. Rows are not
records; fields are not columns; tables are not files.

>> I have a sproc which gathers in one place many calls to different other sprocs, all of them taking a 'StoreGroupe' parameter. <<

Why?? You have forgotten or never learned the very Basics of
programming. Remember why we NEVER begin a name with an underscore?
Remember coupling and cohesion in your first Software Engineering
class?

Since you did not tell us anything about the modules, give us DDL or
even a hint of a specification, it is impossible to tell exactly what
is happening, but these are the basics. However, each module should
handle a NULL parameter on its own. If you reallllly want to write
stinking bad code, then use a CURSOR, keep writing separate modules
that are incomplete, etc.

If you post more specs, you will get more help. Otherwise yiou will
get kludges.|||plz tell me the punchline-- why do we never use underscores?

ps - i agree, loops on the TSQL side are for newbies

Is there any other faster method to compare and update table?

TABLE1 has 5,000,000 records, TABLE2 has 1,000,000 records.
I must compare these two tables and insert to TABLE3 and update TABLE1.
Is there any other faster method can replace the following method?
Thanks.
---
CREATE PROCEDURE RMSTEST1 AS
DECLARE tb1_cursor CURSOR
FOR
SELECT A5,A11,A28,A30 FROM TABLE1
OPEN tb1_cursor
DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF @.V11 IN (SELECT B2 FROM TABLE2)
BEGIN
INSERT INTO TABLE3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
INSERT INTO TABLE3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
END
ELSE
BEGIN
INSERT INTO TABLE3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
INSERT INTO TABLE3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
END
UPDATE TABLE1 SET A39='Y' WHERE CURRENT OF tb1_cursor
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
END
CLOSE tb1_cursor
deallocate tb1_cursorEllen
At first glance a I'd use NOT EXISTS clause to eliminate the rows
SELECT <columns list> FROM Table1
WHERE NOT EXISTS
(SELECT * FROM Table2 WHERE Table1.PK=Table2.PK)
You can insert an output into a temporary table and then to manipulate with
UPDATE statement as you need.
"Ellen" <Ellen@.discussions.microsoft.com> wrote in message
news:EF2570C6-2A36-416E-AF70-BFBB53A20475@.microsoft.com...
> TABLE1 has 5,000,000 records, TABLE2 has 1,000,000 records.
> I must compare these two tables and insert to TABLE3 and update TABLE1.
> Is there any other faster method can replace the following method?
> Thanks.
> ---
> CREATE PROCEDURE RMSTEST1 AS
> DECLARE tb1_cursor CURSOR
> FOR
> SELECT A5,A11,A28,A30 FROM TABLE1
> OPEN tb1_cursor
> DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF @.V11 IN (SELECT B2 FROM TABLE2)
> BEGIN
> INSERT INTO TABLE3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
> INSERT INTO TABLE3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
> END
> ELSE
> BEGIN
> INSERT INTO TABLE3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
> INSERT INTO TABLE3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
> END
> UPDATE TABLE1 SET A39='Y' WHERE CURRENT OF tb1_cursor
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> END
> CLOSE tb1_cursor
> deallocate tb1_cursor|||Please include DDL with your posts otherwise we can only guess at your
table structure and exact requirements. Here's an example, assuming B2
is unique in Table2:
INSERT INTO Table3 (/* ... columns list? */)
SELECT T1.a5, ...
CASE WHEN T2.b2 IS NOT NULL THEN '11110000' ELSE '11120000' END,
CASE WHEN T2.b2 IS NOT NULL THEN '22220000' ELSE '22230000' END,
CASE WHEN T2.b2 IS NOT NULL THEN 'D' ELSE 'C' END
FROM Table1 AS T1
LEFT JOIN Table2 AS AS T2
ON T1.a11 = T2.b2 /* B2 is unique? */
--
David Portas
SQL Server MVP
--|||The full script is:
-- Create Tables
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES3]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES4]
GO
CREATE TABLE [dbo].[SUSTES1] (
[A1] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A2] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A3] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A4] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A5] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A6] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A7] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A8] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A9] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A10] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A11] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A12] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A13] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A14] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A15] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A16] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A17] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A18] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A19] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A20] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A21] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A22] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A23] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A24] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A25] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A26] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A27] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A28] [int] NULL ,
[A29] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A30] [int] NULL ,
[A31] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A32] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A33] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A34] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A35] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A36] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A37] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A38] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A39] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES2] (
[B1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B3] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B4] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B5] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B6] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B7] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B8] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B9] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B11] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B12] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B13] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B14] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B15] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B16] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B17] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B18] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B19] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B20] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B21] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES3] (
[C1] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[C2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[C3] [int] NULL ,
[C4] [char] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES4] (
[D1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[D2] [int] NULL ,
[D3] [int] NULL
) ON [PRIMARY]
GO
--Import Datat
BULK INSERT SUSTES1 FROM 'D:\Table1.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
BULK INSERT SUSTES2 FROM 'D:\Table2.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
--Create INDEX
CREATE INDEX PK_B2
ON SUSTES2(B2)
GO
--Main Process
DECLARE tb1_cursor CURSOR
FOR
SELECT A5,A11,A28,A30 FROM SUSTES1
OPEN tb1_cursor
DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF @.V11 IN (SELECT B2 FROM SUSTES2)
BEGIN
INSERT INTO SUSTES3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
INSERT INTO SUSTES3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
END
ELSE
BEGIN
INSERT INTO SUSTES3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
INSERT INTO SUSTES3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
END
UPDATE SUSTES1 SET A39='Y' WHERE CURRENT OF tb1_cursor
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
END
CLOSE tb1_cursor
deallocate tb1_cursor
--Update Table4
INSERT INTO SUSTES4
SELECT C2,SUM(C3),COUNT(*)
FROM SUSTES3
GROUP BY C2
Ellen
"David Portas" wrote:

> Please include DDL with your posts otherwise we can only guess at your
> table structure and exact requirements. Here's an example, assuming B2
> is unique in Table2:
> INSERT INTO Table3 (/* ... columns list? */)
> SELECT T1.a5, ...
> CASE WHEN T2.b2 IS NOT NULL THEN '11110000' ELSE '11120000' END,
> CASE WHEN T2.b2 IS NOT NULL THEN '22220000' ELSE '22230000' END,
> CASE WHEN T2.b2 IS NOT NULL THEN 'D' ELSE 'C' END
> FROM Table1 AS T1
> LEFT JOIN Table2 AS AS T2
> ON T1.a11 = T2.b2 /* B2 is unique? */
> --
> David Portas
> SQL Server MVP
> --
>|||This should take care of the cursor altogether.
INSERT SUITES3
SELECT A5,CASE WHEN B2 IS NULL THEN 10000 ELSE 0 END +I,A30-A28,J
FROM SUITES1 LEFT JOIN SUITES2 ON SUITES1.A5=SUITES2.B2
CROSS JOIN (SELECT 11110000,'D' UNION SELECT 22220000,'C')X(I,J)
-- WHERE A39='N'
-- UPDATE SUSTES1 SET A39='Y'
-oj
"Ellen Huang" <Ellen Huang@.discussions.microsoft.com> wrote in message
news:BCC149AC-6728-4EF1-B42A-E34C21FF5B8C@.microsoft.com...
> The full script is:
> -- Create Tables
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES3]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES4]
> GO
> CREATE TABLE [dbo].[SUSTES1] (
> [A1] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A2] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A3] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A4] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A5] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A6] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A7] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A8] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A9] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A10] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A11] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A12] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A13] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A14] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A15] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A16] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A17] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A18] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A19] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A20] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A21] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A22] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A23] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A24] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A25] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A26] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A27] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A28] [int] NULL ,
> [A29] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A30] [int] NULL ,
> [A31] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A32] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A33] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A34] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A35] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A36] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A37] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A38] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A39] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES2] (
> [B1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B3] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B4] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B5] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B6] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B7] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B8] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B9] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B10] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B11] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B12] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B13] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B14] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B15] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B16] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B17] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B18] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B19] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B20] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B21] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES3] (
> [C1] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [C2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [C3] [int] NULL ,
> [C4] [char] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES4] (
> [D1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [D2] [int] NULL ,
> [D3] [int] NULL
> ) ON [PRIMARY]
> GO
> --Import Datat
> BULK INSERT SUSTES1 FROM 'D:\Table1.csv'
> WITH (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
> BULK INSERT SUSTES2 FROM 'D:\Table2.csv'
> WITH (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
>
> --Create INDEX
> CREATE INDEX PK_B2
> ON SUSTES2(B2)
> GO
>
> --Main Process
> DECLARE tb1_cursor CURSOR
> FOR
> SELECT A5,A11,A28,A30 FROM SUSTES1
> OPEN tb1_cursor
> DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF @.V11 IN (SELECT B2 FROM SUSTES2)
> BEGIN
> INSERT INTO SUSTES3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
> INSERT INTO SUSTES3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
> END
> ELSE
> BEGIN
> INSERT INTO SUSTES3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
> INSERT INTO SUSTES3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
> END
> UPDATE SUSTES1 SET A39='Y' WHERE CURRENT OF tb1_cursor
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> END
> CLOSE tb1_cursor
> deallocate tb1_cursor
> --Update Table4
> INSERT INTO SUSTES4
> SELECT C2,SUM(C3),COUNT(*)
> FROM SUSTES3
> GROUP BY C2
>
> Ellen
> "David Portas" wrote:
>

Is there an MS SQL Limit function?

MySQL has a convenient syntax for paging data that looks like this:
SELECT * FROM MyTable LIMIT 10, 20
That would select 10 records, starting from record 20, so that it returns records 20 - 30. This is convenient way to page data, without returning anymore rows than than you need.

However, MS SQL doesn't appear to support that syntax. What is the equivalent sql code to select any N rows from an arbitrary starting point, without having to create a stored procedure?

Thanks in advance :)it's a fiasco

SQL Server has the TOP keyword, but it takes only one parameter

see this article -- http://rosca.net/writing/articles/serverside_paging.asp|||How can is start at row 20 when you have not specified an ORDER BY clause?|||How can is start at row 20 when you have not specified an ORDER BY clause?

You can't

Read here

http://weblogs.sqlteam.com/jeffs/category/162.aspx|||http://weblogs.sqlteam.com/jeffs/category/162.aspxjeepers, i took a look at one of the two articles posted there, and boy, that sql is inefficient

brett, did you read the article i posted?|||Which one? I thought the server side paging was pretty good...|||Andrew's code is very,,,need to compare the 2|||i originally read the second one, and it has issues

i just now went back and read the first one, and all it is is a dynamic-ization of the second one

i remain unimpressed

now, did you read the article i posted?|||Yes I did, and it's elegant...but I'd have to test it for performace against some major tables