Need to INSERT twice... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need to INSERT twice...

Author

Topic

ailuro
Starting Member

8 Posts

Posted - 2002-06-01 : 15:52:49

I'm assuming I'm missing something really basic, I just can't seem to figure it out. I am doing an INSERT into a table from an OPENXML recordset, and I need to populate a link table with IDs just entered into the first table. Examples seem to work best, so here goes


INSERT INTO EmailAddress
(EmailTypeID, Address)
SELECT xEmailTypeID, xAddress
FROM OPENXML(@hDoc, 'Persons/Person/Emails/Email[@DBAction="A"]')
WITH (xEmailTypeID int '@EmailTypeID', xAddress varchar(255)  '@Address')

Just a really basic INSERT..SELECT, but now I need to go and
associate those emails just added with the Person in a
PersonEmailAddress table. Since there can be more than one email
address, I can't just use @@IDENTITY because that will only give me
the last. My attempt at a solution doesn't seem to work right:


DECLARE @RowCount int
...Do The Insert Here...

SET @RowCount = @@RowCount

SET ROWCOUNT @RowCount
	INSERT INTO PersonEmail (PersonID, EmailAddressID)
	SELECT @PersonID, EmailAddressID 
	FROM EmailAddress ORDER BY EmailAddressID DESC
SET ROWCOUNT 0

The thinking was that I could get just the last EmailAddressIDs from
the insert by limiting my next INSERT..SELECT using SET ROWCOUNT to
the rowcount of the first insert and then selecting the IDs from
EmailAddress in descending order.

Unfortunatly what I get is the first @RowCount number of IDs from EmailAddress in backwards order (eg: 3, 2, 1 instead of 403, 402, 401), as if the SET ROWCOUNT is limiting the SELECT before the ORDER BY has a chance to take effect.

Is there some other way of getting at this problem that I missed? I'd really rather not have to cursor through the XML simply for speed reasons, though it'd sure make the development easier. I would very much appreciate any insight :)

Thanks!

Edited by - ailuro on 06/02/2002 08:42:03

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-03 : 13:03:45

You need to join the EmailAddress table to the XML doc object you've got there to get the ID's back.

setBasedIsTheTruepath
<O>

ailuro
Starting Member

8 Posts

Posted - 2002-06-03 : 16:24:08

Thanks Set :)
Not entirely sure what you mean, but it got me playing around with it and I somehow managed to get it to work in my test environment. Not entirely sure what's different because it works and it looks almost exactly what I originally posted.

Still, I gotta think there's a better way to put the new IDs from a mass insert into a link table that cares about such things.

-=Ailuro=-

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources