Re: Inserting multiple records into two tables...with a twist
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 30 Jan 2008 22:37:54 +0000 (UTC)
t8ntboy (t8ntboy@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Prev by Date: Re: Dynamic date range for each row?
- Next by Date: Re: Create Table with Variable included in Table Name
- Previous by thread: Re: Inserting multiple records into two tables...with a twist
- Next by thread: Avg() not including 0's
- Index(es):
Relevant Pages
|
