rowcount in ''select into''

Is there a way to insert the rowcount in a 'select into' clause? Example:

select @.@.rowcount as id,* into mytable_with_rowcount from mytable

If mytable has 100 records then I want an id column in 'mytable_with_rowcount' and have the id column numbered 1 - 100. I know this can be done in a loop, but can it be done in the select w/out doing a loop?

Thanks,

Phil

if its sql server 2005 you can use Ranking Function to number the row and then insert to table. Read about Ranking Functions in BOL.

If its 2000, then you can create a table with ID as indentity column and then insert the rows to that table.

http://www.sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk

http://www.sql-server-performance.com/ak_ranking_functions.asp

Madhu

|||Very nice. Thanks.

rowcount help

i'm trying to get total rows found by query that uses top clause...
for example:
select top 10 myTable.* from myTable where myTable.number > 200
let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.
is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().
tnx...> is there any way to get total row count, without affecting the TOP
> clause?
One method is with a subquery:
SELECT TOP 10
myTable.*,
(SELECT COUNT(*)
FROM myTable
WHERE myTable.number > 200) AS TotalRows
FROM myTable
WHERE myTable.number > 200
Hope this helps.
Dan Guzman
SQL Server MVP
"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128944.554703.296710@.38g2000cwa.googlegroups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...
>|||well, it helped...
but i'm worried about the execution time when using full-text search...
anyway, thanx for your help...|||You could select the entire set into a temp table or table variable.
Then, get the count of the temp table, then select the number you want.
e.g.
declare @.tAllRows as table(...)
insert into @.tAllRows
select ... from myTable where myTable.number>200
declare @.iTotalCount int
set @.iTotalCount = @.@.rowcount
select top 10 myTable.*, @.@.rowcount from @.tAllRows
While I usually try to avoid temp tables and table variables,
_sometimes_ they help a lot with performance. Benchmarking would be a
good idea, though, as my approach could be a disaster.
Also, are you paging based on an identity field? If so, you might not
get the results you expect when the numbers are not continuous.|||err...
select top 10 myTable.*, @.iTotalCount from @.tAllRows
my bad...

rowcount help

i'm trying to get total rows found by query that uses top clause...
for example:
select top 10 myTable.* from myTable where myTable.number > 200
let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.
is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().
tnx...> is there any way to get total row count, without affecting the TOP
> clause?
One method is with a subquery:
SELECT TOP 10
myTable.*,
(SELECT COUNT(*)
FROM myTable
WHERE myTable.number > 200) AS TotalRows
FROM myTable
WHERE myTable.number > 200
--
Hope this helps.
Dan Guzman
SQL Server MVP
"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128944.554703.296710@.38g2000cwa.googlegroups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...
>|||well, it helped...
but i'm worried about the execution time when using full-text search...
anyway, thanx for your help...|||You could select the entire set into a temp table or table variable.
Then, get the count of the temp table, then select the number you want.
e.g.
declare @.tAllRows as table(...)
insert into @.tAllRows
select ... from myTable where myTable.number>200
declare @.iTotalCount int
set @.iTotalCount = @.@.rowcount
select top 10 myTable.*, @.@.rowcount from @.tAllRows
While I usually try to avoid temp tables and table variables,
_sometimes_ they help a lot with performance. Benchmarking would be a
good idea, though, as my approach could be a disaster.
Also, are you paging based on an identity field? If so, you might not
get the results you expect when the numbers are not continuous.|||err...
select top 10 myTable.*, @.iTotalCount from @.tAllRows
my bad...

rowcount help

i'm trying to get total rows found by query that uses top clause...

for example:
select top 10 myTable.* from myTable where myTable.number > 200

let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.

is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().

tnx...I answered this question in microsoft.public.sqlserver.server.

It's bad etiquette to post the same question independently to multiple
groups because that causes unnecessary duplication of effort by persons
trying to help. If you have a question appropriate for multiple groups,
specify all groups in a single post so that all responses appear in all
groups.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128056.678229.59640@.i39g2000cwa.googlegro ups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...sql

Row size in SS7

Hello,
I created a table and got the following:
The total row size (15676) for table 'MyTable' exceeds the maximum number of
bytes per row (8060). Rows that exceed the maximum number of bytes will not
be added.
I have a 5 fields that are set to varchar (2000) plus some others. Should I
use the text data type? I was reading that the text data is not stored in
the table but in a separate page. Will this be a problem in doing searches
and pushing data to the web using stored procedures and ASP?
--
Thanks in advance,
StevenUsing the text datatype won't cause you any problems searching or pushing
data; the data's physical storage is of no concern to you when querying the
data.
Whether or not you SHOULD use it is an architectural question that I can't
answer without more information about what kind of database you're building
and what the columns will be used for.
"Steven K" <skaper@.troop.com> wrote in message
news:O82Zp1XpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I created a table and got the following:
> The total row size (15676) for table 'MyTable' exceeds the maximum number
of
> bytes per row (8060). Rows that exceed the maximum number of bytes will
not
> be added.
> I have a 5 fields that are set to varchar (2000) plus some others. Should
I
> use the text data type? I was reading that the text data is not stored in
> the table but in a separate page. Will this be a problem in doing
searches
> and pushing data to the web using stored procedures and ASP?
> --
> Thanks in advance,
> Steven
>|||Using varchar datatype you can have a "declared" row length of larger than
8060. As long as the total combined REAL DATA length does not exceed the
limit, you are fine -- with some composite index you may receive a warning.
However, when the real length of data for a record exceeds the limit, you
will have to use text, ntext or image type. Have a look at "Managing ntext,
text, and image Data" in BOL.
"Steven K" <skaper@.troop.com> wrote in message
news:O82Zp1XpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I created a table and got the following:
> The total row size (15676) for table 'MyTable' exceeds the maximum number
of
> bytes per row (8060). Rows that exceed the maximum number of bytes will
not
> be added.
> I have a 5 fields that are set to varchar (2000) plus some others. Should
I
> use the text data type? I was reading that the text data is not stored in
> the table but in a separate page. Will this be a problem in doing
searches
> and pushing data to the web using stored procedures and ASP?
> --
> Thanks in advance,
> Steven
>sql