> I come from a Microsoft Access background and now they've
> thrown me into SQL with no training. What I am trying to
> do is the following:
>
> <cftransaction>
> <cfquery Insert> - In access I have an autonumber column
> that would get
> updated here automatically.
> <cfquery select> - Here I would go through and selected max(id) as
> lastrecordadded
> </cftransaction>
>
> Now I could use this record to write to some other tables and keep a
> relationship.
>
> But with SQL, it seems that there is no autonumber so when I
> do my first insert, I have a field with type "uniqueid" and
> when I write to that table I pass NewId().
SQL Server has an autonumber data type, but it's called "identity".
> Now how do I retrieve that information? Max() does not want
> to work in my sql routine.
>
> I'm really stuck, and this should seem pretty easy to someone
> who knows SQL.
MAX should work fine in your SQL statement, but there are more efficient
ways to do this than using MAX and CFTRANSACTION. You might instead write an
SQL stored procedure that returns the identity column value for the inserted
record, or a trigger that does the same thing. Here are two examples to get
you started:
1. stored procedure
CREATE PROC sp_insNewRec
@field1 varchar(30),
@field2 varchar(30)
AS
INSERT INTO
mytable
(field1,
field2)
VALUES
(@field1,
@field2)
SELECT
ID = @@IDENTITY
2. insert trigger
CREATE TRIGGER insnewrec ON mytable FOR INSERT AS
SELECT myidfield FROM INSERTED
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
