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
)