I'm doing a two-step process as I insert records from a form.  I first add
quote "header" info into the QUOTES table, then I want to add the multi-
line quote "details" from a grid into a QUOTE_DETAILS table.  The link is
QUOTES.QUOTE_ID.

I'd like to use a SPROC to accomplish this.  How can I insert a record into
QUOTES, get the newly-generated primary key, then use that for the FK as I
insert records into the QUOTE_DETAILS table?

If the primary key is an identity column, then the scope_identity() function
will return the last generated value for your session.

Show quoteHide quote

It depends on how the primary key is being generated. Are you using an
IDENTITY column as primary key? Or a GUID? In case of IDENTITY column, see
@@IDNTITY and SCOPE_IDENTITY() in SQL Server Books Online.

In case of a GUID, see:
http://vyaskn.tripod.com/retrieve_guid_value_like_identity.htm

If something else, let us know.
Show quoteHide quote

Identity column, yes.  Thanks, that works great!

Let's get back to the basics of an RDBMS. Rows are not records and
certainly NOT lines on a form; fields are not columns; tables are not
files; there is no sequential access or ordering in an RDBMS.  The form
has fields; your table has columns and the two of them can have toally
different structures.

line quote "details" from a grid into a QUOTE_DETAILS table.  <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  It is very hard to debug code when you do not let us
see it.

QUOTES, get the newly-generated primary key, then use that for the FK
as I insert records into the QUOTE_DETAILS table? <<

Completely wrong mental model!!!  BY DEFINITION a relational key is a
subset of attributes of an entity; it is discovered or assigned by an
outside authoriuty/  It is not created by putting something into
physical storage.  Sequential record numbers, pointers or other
physical locators in a file system (not an RDBMS) are created by
insertion.

If you decide to use the proprietary, non-relational, unauditable
IDENTITY columns, be suyre to tell the trolls in accounting and legal
about it.  This si the way that Newbie kludge systems to death.

I smell a big, fat compiler bug
How do you rate this script
SET ROWCOUNT!
Squeezing out more performance!!
Number of transactions