Insert statement which uses a return value from an SP as an insert value

I'm quite stuck with this:

I have an import table called ReferenceMatchingImport which contains
data that has been sucked from a data submission. The contents of
this table have to be imported into another table ExternalReference
which has various foreign keys.

This is simple but one of these keys says that the value in
ExternalReference.CompanyRef must be in the CompanyReference table.
Of course if this is an initial import then it will not be so as part
of my script I must insert a new row into CompanyReference and
populate ExternalReference.CompanyRef with the identity column of this
table.

I thought a good idea would be to use an SP which inserts a new row
and returns @.@.Identity as the value to insert. However this doesn't
work as far as I can tell. Is there a approved way to perform this
sort of opperation? My code is below.

Thanks.

ALTER PROCEDURE SP00ReferenceMatchingImport
AS

/*
Just some integrity checking going on here
*/

INSERT ExternalReference
(
ExternalSourceRef,
AssetGroupRef,
CompanyUnitRef,
EntityTypeCode,
CompanyRef, --this is the unknown ref which is returned by the sp
ExternalReferenceTypeCode,
ExternalReferenceCompanyReferenceMapTypeCode,
StartDate,
EndDate,
LastUpdateBy,
LastUpdateDate
)
SELECT rmi.ExternalDataSourcePropertyRef,
rmi.AssetGroup,
rmi.CompanyUnit,
rmi.EntityType,
SP01InsertIPDReference rmi.EntityType, --here I'm trying to run the
sp so that I can use the return value as the insert value
1,
1,
GETDATE(),
GETDATE(),
'RefMatch',
GETDATE()
FROM ReferenceMatchingImport rmi
WHERE rmi.ExternalDataSourcePropertyRef NOT IN (
SELECT ExternalSourceRef
FROM ExternalReference
)Chris Gilbert (chris_q2@.hotmail.com) writes:
> I have an import table called ReferenceMatchingImport which contains
> data that has been sucked from a data submission. The contents of
> this table have to be imported into another table ExternalReference
> which has various foreign keys.
> This is simple but one of these keys says that the value in
> ExternalReference.CompanyRef must be in the CompanyReference table.
> Of course if this is an initial import then it will not be so as part
> of my script I must insert a new row into CompanyReference and
> populate ExternalReference.CompanyRef with the identity column of this
> table.
> I thought a good idea would be to use an SP which inserts a new row
> and returns @.@.Identity as the value to insert. However this doesn't
> work as far as I can tell. Is there a approved way to perform this
> sort of opperation? My code is below.

The best strategy is to use a staging table, and it seems that
ReferenceMatchingImport is this sort of table. So add a column to this
table with the CompanyRef, and before you insert into the
ExternalReference, you create the new company references, and then update
that value in ReferenceMatchingImport.

There are other possible techniques as well, but all boils down to that
you have to get the references before you INSERT. You cannot INSERT into
one table and then with the left hand insert into another table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns955DF29863C85Yazorman@.127.0.0.1>...
> The best strategy is to use a staging table, and it seems that
> ReferenceMatchingImport is this sort of table. So add a column to this
> table with the CompanyRef, and before you insert into the
> ExternalReference, you create the new company references, and then update
> that value in ReferenceMatchingImport.
> There are other possible techniques as well, but all boils down to that
> you have to get the references before you INSERT. You cannot INSERT into
> one table and then with the left hand insert into another table.

Thankyou Erland,

That was enough to make me give up on the approach above and resign
myself to using a cursor. I have done what you suggested and added a
column to the staging table and run the import as above but after
creating the CompanyRefs. The code is below for anyone refering to
this thread.

Erland, is this the approach you were suggesting? Is there a way to
avoid using a cursor?

Thanks, Chris

DECLARE @.ExternalDataSourcePropertyRef VARCHAR(250)
DECLARE @.CompanyRef BIGINT

DECLARE rm_cursor CURSOR FOR
SELECT rmi.ExternalDataSourcePropertyRef
FROM ReferenceMatchingImport rmi
WHERE rmi.CompanyRef IS NULL

OPEN rm_cursor
FETCH NEXT FROM rm_cursor INTO @.ExternalDataSourcePropertyRef

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC @.CompanyRef = SP01InsertCompanyReference 5
UPDATE ReferenceMatchingImport
SET CompanyRef = @.CompanyRef
WHERE ExternalDataSourcePropertyRef = @.ExternalDataSourcePropertyRef

FETCH NEXT FROM rm_cursor INTO @.ExternalDataSourcePropertyRef
END

CLOSE rm_cursor
DEALLOCATE rm_cursor|||Chris Gilbert (chris_q2@.hotmail.com) writes:
> That was enough to make me give up on the approach above and resign
> myself to using a cursor. I have done what you suggested and added a
> column to the staging table and run the import as above but after
> creating the CompanyRefs. The code is below for anyone refering to
> this thread.
> Erland, is this the approach you were suggesting? Is there a way to
> avoid using a cursor?

Probably. Although you make things difficult with using an IDENTITY
column on the CompanyRef table. That makes it more difficult to insert
many rows and know what the keys are. Better is to have artificial key
that you roll your own. Then you could do something like:

CREATE TABLE #extrefs (externalref whatever_type NOT NULL PRIMARY KEY,
ident int IDENTITY UNIQUE,
internalref int NULL)

INSERT #extrefs (externalref)
SELECT DISTINCT externalref FROM ReferenceMatchingImport

SELECT @.maxid = coalesce(MAX(id), 0) FROM RefTable

INSERT RefTable(id, externalref)
SELECT @.maxid + ident, externalref
FROM #extrefs e
WHERE NOT EXISTS (SELECT *
FROM RefTable r
WHERE r.externalref = e.externalref)

UPDATE #extrefs
SET internalref = r.id
FROM #extrefs e
JOIN RefTable r ON r.externalref = e.externalref

Here I am making wild assumptions on how you tables looks like, since I
don't have that information.

> EXEC @.CompanyRef = SP01InsertCompanyReference 5

Side note: in my opinion the return value of a stored procedure should
only be used to indicate status. To return data, use output parameters
instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp