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
.

Relevant Pages

  • Re: Newbie Question on Jobs & Stored Procedures.
    ... whether using CDO or if you want to help me change it to xp_sendmail to look ... >> Hey John, ... >> out when multiple records are returned. ... >> Sql Server. ...
    (microsoft.public.sqlserver.programming)
  • RE: JOIN and one-to-many-to-one
    ... rmCParty.rmCPartyQuantumID) INNER JOIN dbo_cparty ON rmCParty.rmCPartyLikeID ... "Gerald Stanley" wrote: ... Thus I am returning multiple records when I only ... >>> Gerald Stanley MCSD ...
    (microsoft.public.access.queries)
  • Re: First Function
    ... > multiple records. ... "Dan" wrote in message ... > How do I do this is SQL Server 2000? ... >>> I read somewhere that sql server 2000 doesnt support ...
    (microsoft.public.sqlserver.programming)
  • Re: Iterating XML in SQL Server 2005 Stored Procedure
    ... curtin writes: ... SQL2005 that has multiple records that I need to update in a single ... records in the XML document. ... which version of SQL Server are you using? ...
    (microsoft.public.sqlserver.programming)
  • Re: LastNonEmpty Aggregate Function as a count
    ... "The fact table has multiple records for each day and ... the measure values returned are not the aggregate of these .." ... Microsoft MVP - SQL Server ...
    (microsoft.public.sqlserver.olap)