Navigation in a Graph: identity

Friday, March 9, 2012

Need a tip to generate Identity

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_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" 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_tableHow to do that?I use MS-SQL2000.Lilian.

--=_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" = 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

=---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" =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_tableHow to do that?I use MS-SQL2000.Lilian.

--=_NextPart_000_0015_01C3827D.24033A20--

Wednesday, March 7, 2012

Need a Solution

Hi,

How can i define in SqlServer an way to make my identity's columns folow somekind of rule.

I need to define it in sqlserver not in my application code.
So basically what i need is to maintain the inserts i have , assuming that ids are automatic, but in the database being able to modyfing them.
Using trigers or other way, please help me... :)

thanks-.-I assume you don't want to use Sql Server's Identity="Yes" for an int field.

The alternative would be to use a Trigger that fires on Insert that will populate your identity column. The code inside the trigger can follow whatever rule you can code in Transact-Sql.

At least the last time I used Oracle you had to do it that way anyway.


CREATE TRIGGER [MyIdentifyTrigger] ON dbo.YourTable
FOR INSERT
AS
-- Figure out identify value and set column in "inserted" record to that value.

|||Hi,

No, my problem is that i have the Identity set to "Yes" in the fields. I've already seen that with SET IDENTITY_INSERT to ON i can explicit the value to the field with identity.

No, i just need to know the syntax of the trigger to do all of this, when i insert.

I need this because all my inserts in the application assumes auto ids, and now i need to control the value inserted in those field, but i cant go to the code, so must do this in database level.

thanks|||You should be able to accomplish this with an INSTEAD OF trigger. Something like this:


CREATE TRIGGER Insert_Data ON Test
INSTEAD OF INSERT
AS
DECLARE @.ID int, @.Col_SomeText varchar(50)
SELECT @.ID=ID, @.Col_SomeText = SomeText FROM inserted
IF @.ID IS NULL
 BEGIN
 BEGIN TRANSACTION
 SELECT @.ID = IDNumber FROM IDNumberTable HOLDLOCK
 UPDATE IDNumberTable SET IDNumber = IDNumber + 1
 COMMIT
 END
INSERT INTO Test VALUES(@.ID, @.Col_SomeText)

Terri

Friday, March 9, 2012

Need a tip to generate Identity

Wednesday, March 7, 2012

Need a Solution

Navigation in a Graph

Blog Archive

About Me