Knowing @@IDENTITY value during INSERT???

Knowing @@IDENTITY value during INSERT???

Post by Henr » Wed, 15 Nov 2000 04:00:00



I have a database table 'Foo' which primary key IDENTITY 'FooID' of type INT
that gets auto incremented with every insertion.  I also store a file name
in column 'FooFile' of table 'Foo' that has the FooID appended to it.


primary key 'FooID' and then do an Update on that entry for the file name in

query so that I can do everything in the Insert query instead of using both
the Insert & Update query?  Thanks.

 
 
 

Knowing @@IDENTITY value during INSERT???

Post by Umachandar Jayachandra » Wed, 15 Nov 2000 04:00:00


    Don't store the FooID with FooFile! Use a computed column instead like:

CREATE TABLE foobar (
    FooID int IDENTITY ,
    FooFile varchar( 30 ) ,
    FooFileWithID AS ( FooFile + '-' + CAST( FooID AS varchar ) )
)
    The disadvantage with this approach is that you can't index
"FooFileWithID" in SQL70. But you can index the column in SQL2000.
    Otherwise, there is no other way to get the next value since that would
mean kind of locking that particular value. Anyway there is no way to get
current value.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)

 
 
 

Knowing @@IDENTITY value during INSERT???

Post by Aaron Bertrand [MVP » Wed, 15 Nov 2000 04:00:00


Why not just concatenate the filename and identity columns at query time?

--
The ASP FAQ has a new look.  http://www.aspfaq.com/


> I have a database table 'Foo' which primary key IDENTITY 'FooID' of type
INT
> that gets auto incremented with every insertion.  I also store a file name
> in column 'FooFile' of table 'Foo' that has the FooID appended to it.


the
> primary key 'FooID' and then do an Update on that entry for the file name
in

> query so that I can do everything in the Insert query instead of using
both
> the Insert & Update query?  Thanks.

 
 
 

Knowing @@IDENTITY value during INSERT???

Post by Henr » Thu, 16 Nov 2000 04:00:00


Exactly. However, I need to know the identity column value upon the INSERT
query execution.  Once I know that, then I can create the file name.  So my
question is how do I know the identity value within the INSERT query?  maybe
i didn't quite understand your question.



> Why not just concatenate the filename and identity columns at query time?

> --
> The ASP FAQ has a new look.  http://www.aspfaq.com/



> > I have a database table 'Foo' which primary key IDENTITY 'FooID' of type
> INT
> > that gets auto incremented with every insertion.  I also store a file
name
> > in column 'FooFile' of table 'Foo' that has the FooID appended to it.


> the
> > primary key 'FooID' and then do an Update on that entry for the file
name
> in

> > query so that I can do everything in the Insert query instead of using
> both
> > the Insert & Update query?  Thanks.

 
 
 

Knowing @@IDENTITY value during INSERT???

Post by Aaron Bertrand [MVP » Thu, 16 Nov 2000 04:00:00


Quote:> Exactly. However, I need to know the identity column value upon the INSERT
> query execution.

My point was that there's no reason to duplicate the data that's in the
table.  Why have an ID of 322 in 2 places, especially when it requires a
more complex INSERT?

You can do as follows, when you actually need to "build" a filename at query
time (NOT at insert time):

SELECT filename_Field+CONVERT(varchar(10),IDENTITY_FIELD) as Filename...

Instead of this:

SELECT filename_Field as Filename...

And then you don't have to worry about (a) redundant data, which requires
more storage space, and (b) more complex insert operations.

--
The FAQ has a new look.  http://www.aspfaq.com/



> > Why not just concatenate the filename and identity columns at query

time?
 
 
 

Knowing @@IDENTITY value during INSERT???

Post by Henr » Fri, 17 Nov 2000 04:00:00


Oh ok. I understand what you're saying. However, I need that file name in
order to really name my uniquely named files since all of these files are
stored in the same directory.  Hence, I'm relying on the unique IDENTITY to
make my file names unique as well.  It's for auditing purposes too so that I
have the exact file name stored in that field instead so that my audit
programs simply look at that complete file name instead of having to do any
business programming on concatenating that file name with the identity to
get the true file name.  I also need to deal with different file extensions.
With your idea, I would need to store another column for file extension but
i would hope to avoid that and simply store the complete name of the file in
one field.



> > Exactly. However, I need to know the identity column value upon the
INSERT
> > query execution.

> My point was that there's no reason to duplicate the data that's in the
> table.  Why have an ID of 322 in 2 places, especially when it requires a
> more complex INSERT?

> You can do as follows, when you actually need to "build" a filename at
query
> time (NOT at insert time):

> SELECT filename_Field+CONVERT(varchar(10),IDENTITY_FIELD) as Filename...

> Instead of this:

> SELECT filename_Field as Filename...

> And then you don't have to worry about (a) redundant data, which requires
> more storage space, and (b) more complex insert operations.

> --
> The FAQ has a new look.  http://www.aspfaq.com/


message

> > > Why not just concatenate the filename and identity columns at query
> time?

 
 
 

1. Getting the value of an autoincrement or identity field during an sql insert

Please forgive me if I have asked a common question, but Dejanews was no
help (yes I looked :> )

If I am using an SQL insert inside of VB with dao for instance connected via
ODBC to sqlserver7 database, how do I get the value of the identity or
autoincrement field as the record is added.  I realize that using the
.addnew method on the recordset will make the value of the identity field
immediately available.  However I want to use the .execute method with the
database i.e db.execute("INSERT " & yada yada yada)  Now, that I have just
executed the insert statement, how can I get the value of the AutoNumber
field that is a part of that record.  I can't just look at the last record
as I have 25 concurrent users all adding records and by the time I re-query,
I may have an additional 25 records on top of the one just added in this
example.

Please help me.
Chris


remove the .no.Spam to make it work

2. HELP !!!

3. Determine IDENTITY value during multiple INSERTS

4. How can I build an .ADE from .ADP by VBA?

5. Getting the value of an autoincrement or identity field during an sql insert

6. Database lookup control

7. Attempt to insert duplicate identity values in table (has identity and key fields)

8. How to program an ODBC driver ?

9. INSERT INTO with SELECT DISTINCT causes a zero value to be inserted into an IDENTITY column

10. Setting field value during insert trigger

11. How to replicate values between columns during INSERT

12. Trigger:set a field value during INSERT

13. DBGrid1.Fields[0] - Where's The Value During Insert/Before Post