|
|
Posted by Erland Sommarskog on 01/30/08 22:37
t8ntboy (t8ntboy@gmail.com) writes:
> ASP/SQL Server Express 05
>
> I have two tables, A and B. I would like to insert multiple records
> into both form A and B, but a field in form B is the Record ID in form
> A. So, I need to insert the records into A, collect the ID and then
> insert into B. I have no problem doing this for individual records;
> I am not sure what the best way to this is for multiple records being
> inserted at the same time.
>
> How would you accomplish this if you were me?
I assume that you have an IDENTITY column, since you ask. What I would
do, would be to change the IDENTITY column to be a regular integer column,
unless there really is a pressing reason for using IDENTITY. (That pressing
reason would typically be a high rate of concurrent insertions.)
In that case, inserting into the two tables is simple:
BEGIN TRANSACTION
SELECT @maxid = MAX(coalesce(id, 0)) FROM tblA WITH (UPDLOCK)
INSERT tblA(id, col1, col2, ...)
SELECT @maxid + row_number() OVER(ORDER BY src.somecol1, somecol2),
src.col1, src.col2, ...
FROM src
INSERT tblB(id, col1, col2, ...
SELECT src.idA, srcb.col1, srcb.col2
FROM (SELECT @maxid + row_number()
OVER(ORDER BY src.somecol1, somecol2) AS idA,
col1, col2, ...) AS src
JOIN srcb ON ...
COMMIT TRANSACTION
If you can't change the table, you can use the OUTPUT clause:
INSERT tblA(col1, col2, col3, ....)
OUTPUT inserted.id, inserted.col1, .... INTO @tmp
SELECT col1, col2, ...
FROM src
INSERT tblB(idA, col1, col2, ...
SELECT t.id, srcb.col2, srcb.col2, ...
FROM srcb b
JOIN @tmp t
The problem with the latter is that it will only work if you insert what
uniquely identifies the source into the target table, as you cannot
refer to columns from the SELECT statement that are not inserted in
the OUTPUT clause. For instance, if the source includes a row number in a
grid from your data set, you are not likely to insert that in the target
table.
In the first method, the sole requirement is that the source has some
unique idenfification, and it is this unique identification you
specify in the ORDER BY clause.
--
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
Navigation:
[Reply to this message]
|