Insert @@Identity back into same record? 
Author Message
 Insert @@Identity back into same record?
I programming a web site site session tracking system where the sessions
table looks like this:

ID (auto int)
DATESTAMP
PREVIOUSID

On a first web site session, there is no PreviousID value, but I want to

I want to know if there's a one-step method to doing this, instead of
running a transaction, doing the first insert, getting the identity, and
then doing an update of that first insert.



Fri, 06 Jan 2006 20:52:23 GMT
 Insert @@Identity back into same record?

You have 2 databases?
In first database you insert records and PREVIOUS id is ID of the recodr previously inserted?
In second database you wan't to do what?

Be more precise!

--
Dean Savovic
www.teched.hr

Quote:

> I programming a web site site session tracking system where the sessions
> table looks like this:

> ID (auto int)
> DATESTAMP
> PREVIOUSID

> On a first web site session, there is no PreviousID value, but I want to

> I want to know if there's a one-step method to doing this, instead of
> running a transaction, doing the first insert, getting the identity, and
> then doing an update of that first insert.



Sat, 07 Jan 2006 09:01:03 GMT
 Insert @@Identity back into same record?
You're reading too much into it.  There's just 1 table, and I'm inserting 1




Quote:
> You have 2 databases?
> In first database you insert records and PREVIOUS id is ID of the recodr

previously inserted?
Quote:
> In second database you wan't to do what?

> Be more precise!

> --
> Dean Savovic
> www.teched.hr




Quote:
> > I programming a web site site session tracking system where the sessions
> > table looks like this:

> > ID (auto int)
> > DATESTAMP
> > PREVIOUSID

> > On a first web site session, there is no PreviousID value, but I want to

> > I want to know if there's a one-step method to doing this, instead of
> > running a transaction, doing the first insert, getting the identity, and
> > then doing an update of that first insert.



Sat, 07 Jan 2006 18:45:17 GMT
 Insert @@Identity back into same record?
Hi

It is hard to understand why you are doing this.

If you already have an identity column then the value will be populated by
the insert.

If you want to have a copy of that column try using a view or a computed
column.

e.g (untested)

CREATE TABLE MyIDTable (
    Id INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
    Col1 varchar(10),
    StrId AS 'S' + RIGHT('000000', CONVERT(VARCHAR,id) ) )

John


Quote:
> You're reading too much into it.  There's just 1 table, and I'm inserting
1


insert.
> Then, I have to go back and update the record to add it once I get

add



> > You have 2 databases?
> > In first database you insert records and PREVIOUS id is ID of the recodr
> previously inserted?
> > In second database you wan't to do what?

> > Be more precise!

> > --
> > Dean Savovic
> > www.teched.hr



> > > I programming a web site site session tracking system where the
sessions
> > > table looks like this:

> > > ID (auto int)
> > > DATESTAMP
> > > PREVIOUSID

> > > On a first web site session, there is no PreviousID value, but I want
to

> > > I want to know if there's a one-step method to doing this, instead of
> > > running a transaction, doing the first insert, getting the identity,
and
> > > then doing an update of that first insert.



Sat, 07 Jan 2006 20:04:58 GMT
 Insert @@Identity back into same record?
Here's an example of records:

ID            PREVIOUSID
1234        null
1512        1234
1581        1234
1851        1234

Sessions 2-4 show that the first time the person was on the site, it was
under session 1234.   Now, if I want to count how many times the person
visited the site, I can do COUNT(PREVIOUSID) grouped by PREVIOUSID.    The
problem, of course, is when I insert that first 1234, I can't insert a NULL.
I want to insert:

1234        1234

Then my counts and group by statements will work.

So, how would I do that?

INSERT INTO WHATEVER
(field2)
VALUES
(????)

Can I just say "field1" where I wrote ????


Quote:
> Hi

> It is hard to understand why you are doing this.

> If you already have an identity column then the value will be populated by
> the insert.

> If you want to have a copy of that column try using a view or a computed
> column.

> e.g (untested)

> CREATE TABLE MyIDTable (
>     Id INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
>     Col1 varchar(10),
>     StrId AS 'S' + RIGHT('000000', CONVERT(VARCHAR,id) ) )

> John



> > You're reading too much into it.  There's just 1 table, and I'm
inserting
> 1


> insert.
> > Then, I have to go back and update the record to add it once I get

> add



> > > You have 2 databases?
> > > In first database you insert records and PREVIOUS id is ID of the
recodr
> > previously inserted?
> > > In second database you wan't to do what?

> > > Be more precise!

> > > --
> > > Dean Savovic
> > > www.teched.hr



> > > > I programming a web site site session tracking system where the
> sessions
> > > > table looks like this:

> > > > ID (auto int)
> > > > DATESTAMP
> > > > PREVIOUSID

> > > > On a first web site session, there is no PreviousID value, but I
want
> to

> > > > I want to know if there's a one-step method to doing this, instead
of
> > > > running a transaction, doing the first insert, getting the identity,
> and
> > > > then doing an update of that first insert.



Sun, 08 Jan 2006 01:13:55 GMT
 Insert @@Identity back into same record?
Hi

I seems that the ID is being used as a UserId type field. In which case I
would expect there to be two tables one for users and one for sessions.
There will always be a check for the user existing and that will get their

versions) would be used to for the ID field.

If you stick with this design then your select statement could use:

CREATE TABLE #Test ( ID INT NOT NULL, PREVIOUS_ID INT )

INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 1, NULL )
INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 2, 1 )
INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 3, 1 )
INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 3, 1 )
INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 5, NULL )
INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 6, NULL )
INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 7, 6 )
INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 8, 6 )
INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 9, 6 )
INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 10, 6 )

SELECT ISNULL(PREVIOUS_ID,ID), COUNT(ISNULL(PREVIOUS_ID,ID))
FROM #Test
GROUP BY ISNULL(PREVIOUS_ID,ID)

DROP TABLE #Test

John


Quote:
> Here's an example of records:

> ID            PREVIOUSID
> 1234        null
> 1512        1234
> 1581        1234
> 1851        1234

> Sessions 2-4 show that the first time the person was on the site, it was
> under session 1234.   Now, if I want to count how many times the person
> visited the site, I can do COUNT(PREVIOUSID) grouped by PREVIOUSID.    The
> problem, of course, is when I insert that first 1234, I can't insert a
NULL.
> I want to insert:

> 1234        1234

> Then my counts and group by statements will work.

> So, how would I do that?

> INSERT INTO WHATEVER
> (field2)
> VALUES
> (????)

> Can I just say "field1" where I wrote ????



> > Hi

> > It is hard to understand why you are doing this.

> > If you already have an identity column then the value will be populated
by
> > the insert.

> > If you want to have a copy of that column try using a view or a computed
> > column.

> > e.g (untested)

> > CREATE TABLE MyIDTable (
> >     Id INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
> >     Col1 varchar(10),
> >     StrId AS 'S' + RIGHT('000000', CONVERT(VARCHAR,id) ) )

> > John



> > > You're reading too much into it.  There's just 1 table, and I'm
> inserting
> > 1


> > insert.
> > > Then, I have to go back and update the record to add it once I get

I
> > add



> > > > You have 2 databases?
> > > > In first database you insert records and PREVIOUS id is ID of the
> recodr
> > > previously inserted?
> > > > In second database you wan't to do what?

> > > > Be more precise!

> > > > --
> > > > Dean Savovic
> > > > www.teched.hr



> > > > > I programming a web site site session tracking system where the
> > sessions
> > > > > table looks like this:

> > > > > ID (auto int)
> > > > > DATESTAMP
> > > > > PREVIOUSID

> > > > > On a first web site session, there is no PreviousID value, but I
> want
> > to

> > > > > I want to know if there's a one-step method to doing this, instead
> of
> > > > > running a transaction, doing the first insert, getting the
identity,
> > and
> > > > > then doing an update of that first insert.



Sun, 08 Jan 2006 08:48:40 GMT
 Insert @@Identity back into same record?
If you have this logic of session auditing then use the following to see how many times the user has visited the site:

create table sessions
(id int not null primary key
, previousID int)

insert into sessions
select 1234, null
union
select 1235, 1234
union
select 1236,1234
union
select 1237, 1234

select count(previousID) + 1
from sessions
where previousID = 1234

drop table sessions

I think that it would not be right to have in record that records the first visit ID equal to previousID, because in that moment
there are no previous ID's.

--
Dean Savovic
www.teched.hr

Quote:

> Here's an example of records:

> ID            PREVIOUSID
> 1234        null
> 1512        1234
> 1581        1234
> 1851        1234

> Sessions 2-4 show that the first time the person was on the site, it was
> under session 1234.   Now, if I want to count how many times the person
> visited the site, I can do COUNT(PREVIOUSID) grouped by PREVIOUSID.    The
> problem, of course, is when I insert that first 1234, I can't insert a NULL.
> I want to insert:

> 1234        1234

> Then my counts and group by statements will work.

> So, how would I do that?

> INSERT INTO WHATEVER
> (field2)
> VALUES
> (????)

> Can I just say "field1" where I wrote ????



> > Hi

> > It is hard to understand why you are doing this.

> > If you already have an identity column then the value will be populated by
> > the insert.

> > If you want to have a copy of that column try using a view or a computed
> > column.

> > e.g (untested)

> > CREATE TABLE MyIDTable (
> >     Id INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
> >     Col1 varchar(10),
> >     StrId AS 'S' + RIGHT('000000', CONVERT(VARCHAR,id) ) )

> > John



> > > You're reading too much into it.  There's just 1 table, and I'm
> inserting
> > 1


> > insert.
> > > Then, I have to go back and update the record to add it once I get

> > add



> > > > You have 2 databases?
> > > > In first database you insert records and PREVIOUS id is ID of the
> recodr
> > > previously inserted?
> > > > In second database you wan't to do what?

> > > > Be more precise!

> > > > --
> > > > Dean Savovic
> > > > www.teched.hr



> > > > > I programming a web site site session tracking system where the
> > sessions
> > > > > table looks like this:

> > > > > ID (auto int)
> > > > > DATESTAMP
> > > > > PREVIOUSID

> > > > > On a first web site session, there is no PreviousID value, but I
> want
> > to

> > > > > I want to know if there's a one-step method to doing this, instead
> of
> > > > > running a transaction, doing the first insert, getting the identity,
> > and
> > > > > then doing an update of that first insert.



Sun, 08 Jan 2006 08:58:19 GMT
 Insert @@Identity back into same record?
Thanks, but is there a way to do the insert in one step if I want to, where
"previousid" is not left as Null?

Your count works as you said (+1), but I actually have many queries to write
(e.g. Select * from Table where PreviousID = 1234), and I will need a
solution to the original question (the first sentence above).


Quote:
> If you have this logic of session auditing then use the following to see

how many times the user has visited the site:
Quote:

> create table sessions
> (id int not null primary key
> , previousID int)

> insert into sessions
> select 1234, null
> union
> select 1235, 1234
> union
> select 1236,1234
> union
> select 1237, 1234

> select count(previousID) + 1
> from sessions
> where previousID = 1234

> drop table sessions

> I think that it would not be right to have in record that records the

first visit ID equal to previousID, because in that moment
Quote:
> there are no previous ID's.

> --
> Dean Savovic
> www.teched.hr




- Show quoted text -

Quote:
> > Here's an example of records:

> > ID            PREVIOUSID
> > 1234        null
> > 1512        1234
> > 1581        1234
> > 1851        1234

> > Sessions 2-4 show that the first time the person was on the site, it was
> > under session 1234.   Now, if I want to count how many times the person
> > visited the site, I can do COUNT(PREVIOUSID) grouped by PREVIOUSID.
The
> > problem, of course, is when I insert that first 1234, I can't insert a
NULL.
> > I want to insert:

> > 1234        1234

> > Then my counts and group by statements will work.

> > So, how would I do that?

> > INSERT INTO WHATEVER
> > (field2)
> > VALUES
> > (????)

> > Can I just say "field1" where I wrote ????



> > > Hi

> > > It is hard to understand why you are doing this.

> > > If you already have an identity column then the value will be
populated by
> > > the insert.

> > > If you want to have a copy of that column try using a view or a
computed
> > > column.

> > > e.g (untested)

> > > CREATE TABLE MyIDTable (
> > >     Id INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
> > >     Col1 varchar(10),
> > >     StrId AS 'S' + RIGHT('000000', CONVERT(VARCHAR,id) ) )

> > > John



> > > > You're reading too much into it.  There's just 1 table, and I'm
> > inserting
> > > 1

insert

> > > insert.
> > > > Then, I have to go back and update the record to add it once I get

Can I
> > > add



> > > > > You have 2 databases?
> > > > > In first database you insert records and PREVIOUS id is ID of the
> > recodr
> > > > previously inserted?
> > > > > In second database you wan't to do what?

> > > > > Be more precise!

> > > > > --
> > > > > Dean Savovic
> > > > > www.teched.hr



> > > > > > I programming a web site site session tracking system where the
> > > sessions
> > > > > > table looks like this:

> > > > > > ID (auto int)
> > > > > > DATESTAMP
> > > > > > PREVIOUSID

> > > > > > On a first web site session, there is no PreviousID value, but I
> > want
> > > to

> > > > > > I want to know if there's a one-step method to doing this,
instead
> > of
> > > > > > running a transaction, doing the first insert, getting the
identity,
> > > and
> > > > > > then doing an update of that first insert.



Sun, 08 Jan 2006 20:13:07 GMT
 Insert @@Identity back into same record?

You can use an insert trigger:

create table t
(
 id int identity
   primary key
, parent int not null
   default -1
)
go

create trigger tri_t on t after insert
as
update t
set
 parent = i.id
from
 inserted i
where
 i.id  = t.id
and i.parent = -1
go

insert t (parent) values (-1)
insert t (parent) values (1)

select * from t
go

drop table t

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

Thanks, but is there a way to do the insert in one step if I want to, where
"previousid" is not left as Null?

Your count works as you said (+1), but I actually have many queries to write
(e.g. Select * from Table where PreviousID = 1234), and I will need a
solution to the original question (the first sentence above).


Quote:
> If you have this logic of session auditing then use the following to see

how many times the user has visited the site:
Quote:

> create table sessions
> (id int not null primary key
> , previousID int)

> insert into sessions
> select 1234, null
> union
> select 1235, 1234
> union
> select 1236,1234
> union
> select 1237, 1234

> select count(previousID) + 1
> from sessions
> where previousID = 1234

> drop table sessions

> I think that it would not be right to have in record that records the

first visit ID equal to previousID, because in that moment
Quote:
> there are no previous ID's.

> --
> Dean Savovic
> www.teched.hr




- Show quoted text -

Quote:
> > Here's an example of records:

> > ID            PREVIOUSID
> > 1234        null
> > 1512        1234
> > 1581        1234
> > 1851        1234

> > Sessions 2-4 show that the first time the person was on the site, it was
> > under session 1234.   Now, if I want to count how many times the person
> > visited the site, I can do COUNT(PREVIOUSID) grouped by PREVIOUSID.
The
> > problem, of course, is when I insert that first 1234, I can't insert a
NULL.
> > I want to insert:

> > 1234        1234

> > Then my counts and group by statements will work.

> > So, how would I do that?

> > INSERT INTO WHATEVER
> > (field2)
> > VALUES
> > (????)

> > Can I just say "field1" where I wrote ????



> > > Hi

> > > It is hard to understand why you are doing this.

> > > If you already have an identity column then the value will be
populated by
> > > the insert.

> > > If you want to have a copy of that column try using a view or a
computed
> > > column.

> > > e.g (untested)

> > > CREATE TABLE MyIDTable (
> > >     Id INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
> > >     Col1 varchar(10),
> > >     StrId AS 'S' + RIGHT('000000', CONVERT(VARCHAR,id) ) )

> > > John



> > > > You're reading too much into it.  There's just 1 table, and I'm
> > inserting
> > > 1

insert

> > > insert.
> > > > Then, I have to go back and update the record to add it once I get

Can I
> > > add



> > > > > You have 2 databases?
> > > > > In first database you insert records and PREVIOUS id is ID of the
> > recodr
> > > > previously inserted?
> > > > > In second database you wan't to do what?

> > > > > Be more precise!

> > > > > --
> > > > > Dean Savovic
> > > > > www.teched.hr



> > > > > > I programming a web site site session tracking system where the
> > > sessions
> > > > > > table looks like this:

> > > > > > ID (auto int)
> > > > > > DATESTAMP
> > > > > > PREVIOUSID

> > > > > > On a first web site session, there is no PreviousID value, but I
> > want
> > > to

> > > > > > I want to know if there's a one-step method to doing this,
instead
> > of
> > > > > > running a transaction, doing the first insert, getting the
identity,
> > > and
> > > > > > then doing an update of that first insert.



Sun, 08 Jan 2006 20:20:49 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Insert new rec, then pass new identity val back to Access97

2. get back the identity of a newly inserted row thru ODBC

3. Identity -- Getting it back on Insert

4. Getting back identity after an insert via ADO?

5. Insert new rec, then pass new identity val back to Access97

6. Getting Back Identity column value on Insert using RDS

7. Getting back an identity field value after an insert SQL statement

8. reading the record back after an insert.

9. Insert record specifying IDENTITY using ADO - Help!

10. Inserting a record w/ identity column via MS Query

11. Getting the currently inserted record-id of a IDENTITY-Table

12. Insert record to a table with identity field?


 
Powered by phpBB® Forum Software