Inserting multiple records into two tables...with a twist

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?

Any help would be appreciated tremendously.

Re: Inserting multiple records into two tables...with a twist

On Wed, 30 Jan 2008 13:18:26 -0800 (PST), t8ntboy wrote:

>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?
>
>Any help would be appreciated tremendously.

Hi t8ntboy,

Assuming that TableA_ID is the generated surrogate key and TableA_Key is
the "real" key, you can fetch the generated surrogate key values by
querying TableA "after" the insert with a join on the table with
inserted values, like this:

SELECT A.TableA_ID, (other column)
FROM TableA AS A
INNER JOIN Staging_Table AS S
ON S.TableA_Key = A.TableA_Key;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Re: Inserting multiple records into two tables...with a twist

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Inserting multiple records into two tables...with a twist

>> I have two tables, A and B. <<

Mind showing us their DDL?

>> I would like to insert multiple records [sic:rows are not records] into both form A [what is a form? It is not an SQL term!] and B, but a field [sic:fields are not columns] in form B is the Record ID [sic: RDBMS uses keys, not exposed physical record locators] in form A. <<

Exactly what product are you using? You are describing something that
is not SQL.

>> 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. <<

What do you mean "collect the ID"? In a relational DB, the key is a
subset of the attributes of the entity, an inherent part of it and not
something you let the hardware create when you insert a row. Next, a
table, which is nothing like a file; it is the set of one and only one
kind of entity. Thus having the same data in two tables is a
violation of basic RDBMS design -- redundancy is to be avoided via
Normal Forms and other design methods.

I would guess that you have screwed up everything because you are not
writing SQL at all. As long as you had row-at-time insertions, you
could fake a traditional file system and treat rows as if they were
records. But when you insert a set, you found out that the "set" is
the unit of work; it goes into the table all at once, in no particular
order. That is only one of many ways that rows are not records, and
tables are not files.

>> How would you accomplish this if you were me? Any help would be appreciated tremendously. <<

Learn the basics; newsgroups are places to get kludges and not an
education.

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. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. 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.

If you want to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

Re: Inserting multiple records into two tables...with a twist

--CELKO-- wrote:

>>> 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. <<
>
> What do you mean "collect the ID"? In a relational DB, the key is a
> subset of the attributes of the entity, an inherent part of it and not
> something you let the hardware create when you insert a row. Next, a
> table, which is nothing like a file; it is the set of one and only one
> kind of entity. Thus having the same data in two tables is a
> violation of basic RDBMS design -- redundancy is to be avoided via
> Normal Forms and other design methods.

It sounds like he intends B to have a foreign key referencing A.

Also, one of these days, I really do need to write that "what do 'rows
are not records' and 'columns are not fields' actually mean?" FAQ.