I need the same functionality as IDENTITY(int,1,1) but only in a SELECT
(without the INTO table part)
I want to do something like this:
select IDENTITY(int,1,1) as ident, * from my_table
How to do that?
I use MS-SQL2000.
Lilian.This is a multi-part message in MIME format.
--=_NextPart_000_0AC4_01C381C4.ABEE7350
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Try:
select
(select count(*) from MyTable as i
where i.PK <=3D o.PK) as ident
, o.*
from
MyTable as o
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message =news:#TlBUTegDHA.1872@.TK2MSFTNGP09.phx.gbl...
Hi all,
I need the same functionality as IDENTITY(int,1,1) but only in a SELECT
(without the INTO table part)
I want to do something like this:
select IDENTITY(int,1,1) as ident, * from my_table
How to do that?
I use MS-SQL2000.
Lilian.
--=_NextPart_000_0AC4_01C381C4.ABEE7350
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Try:
select
(select =count(*) from MyTable as i
= where i.PK <=3D o.PK) as ident
, =o.*
from
MyTable as o
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Lilian Pigallio"
--=_NextPart_000_0AC4_01C381C4.ABEE7350--|||this is a workable solution, but why don't you want to use
SELECT INTO with identity function? because solution
suggested by Tom will be very slow if the resultset is
really large. In that case SELECT INTO result table and
then getting selecting from result table will run faster.
>--Original Message--
>Try:
>select
> (select count(*) from MyTable as i
> where i.PK <= o.PK) as ident
>, o.*
>from
> MyTable as o
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:#TlBUTegDHA.1872@.TK2MSFTNGP09.phx.gbl...
>Hi all,
>I need the same functionality as IDENTITY(int,1,1) but
only in a SELECT
>(without the INTO table part)
>I want to do something like this:
> select IDENTITY(int,1,1) as ident, * from my_table
>
>How to do that?
>I use MS-SQL2000.
>Lilian.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C3827D.24033A20
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks :^)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23NGj2YegDHA.2072@.TK2MSFTNGP10.phx.gbl...
Try:
select
(select count(*) from MyTable as i
where i.PK <=3D o.PK) as ident
, o.*
from
MyTable as o
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message =news:#TlBUTegDHA.1872@.TK2MSFTNGP09.phx.gbl...
Hi all,
I need the same functionality as IDENTITY(int,1,1) but only in a =SELECT
(without the INTO table part)
I want to do something like this:
select IDENTITY(int,1,1) as ident, * from my_table
How to do that?
I use MS-SQL2000.
Lilian.
--=_NextPart_000_0015_01C3827D.24033A20
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks :^)
"Tom Moreau"
Try:
select
(select =count(*) from MyTable as i
= where i.PK <=3D o.PK) as ident
, =o.*
from
MyTable as = o
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Lilian Pigallio"
--=_NextPart_000_0015_01C3827D.24033A20--
No comments:
Post a Comment