Posted by Erland Sommarskog on 12/02/06 23:34

Wes Groleau (groleau+news@freeshell.org) writes:
> INSERT INTO X ...... ( A, B, C )
> INSERT INTO Y ...... ( J, K, L )
>
> If Y has a foreign key M which is the primary key D of X,
> is there an easy and/or efficient way to have SQL Server
> assign D, and tell us what it is so we can add M to the
> list for Y ?
>
> I know I can select D where A, B, C but I wondered about
> other tricks.

The first question is: why would you want SQL Server to assign the
primary key?

Normally, the primary key of tbe table is to be found in the data. For
instance, if you have a table EmployeeProjects which specifies which
employee that are members of which projects, then that table would
have a primary of (EmployeeID, ProjectCode), and there would not be
any one-column key.

Thus, in the example above, the columns A, B and C would be the primary
key in X, and they would appear in Y as well,

There are of course cases where a system-generated surrogate key is
called for. For instance, the natural key may be difficult to determine
exactly, or it may be mutable. In the cases of multi-column keys, you
may sometimes prefer to introduce a surrogate key to reduce the size
of foreign keys in referring tables. Personally, I've more or less adding
surrogate keys of this reason to my tables, because I've seen cases
where it did more harm than benefit.

If you need to use surrogate keys, it's often best to roll your own:

BEGIN TRANSACTION

SELECT @D = coaelsce(MAX(D), 0) + 1 FROM X WITH (UPDLOCK, HOLDLOCK)

INSERT X (A, B, C, D, ...)
VALUES (..., @D, ...)

INSERT Y (J, K, L, M, ...)
VALUES(..., @D, ...)

COMMIT TRANSACTION

This can easily be extended if you insert more than one row at a time.

To have SQL Server to generate the surrogate key for you, you can use
the IDENTITY property. This is particularly a good idea, if you expect
plenty of concurrent inserts. The code above serlializes parallel
inserts on the SELECT statement. In this case you do:

BEGIN TRANSACTION

INSERT X (A, B, C, ...)
VALUES (...)

SELECT @D = scope_identity()

INSERT Y (J, K, L, M, ...)
VALUES(..., @D, ...)

COMMIT TRANSACTION

But this only works for single-row inserts. For multi-row inserts, you
are better off mapping from the natural keys in X. And if there are no
natural keys in X to map back to, you are in trouble. Overlall, there
several places where IDENTITY gives you issues when rolling your own
does not, so I recommend that you only use it when concurrency calls
for it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]