February 10, 2016 at 6:42 am
Hi there, I am using the following Cursor in order to populate multiple tables.
/*
This script populates the following tables in Cx. They are listed in the order that they import.
Asset
AssetAddress
AssetCompany
Import Referance
*/
--Cursor Variables
--DECLARE @SourceRef as varchar (50)
DECLARE @AssetReference as varchar (50)
DECLARE @AssetType as int
DECLARE @AssetDescription as varchar (200)
DECLARE @GeoLocation as varchar (500)
DECLARE @ActualHandOverDate as datetime
DECLARE @ConstructionDate as datetime
DECLARE @LocalAuthority int
DECLARE @County as int
DECLARE @Base1999Valuation as decimal (18,2)
DECLARE @NumberofBedrooms as int
DECLARE @ClassificationType as int
DECLARE @CategoryType as int
DECLARE @Address as int
DECLARE @Country as int
DECLARE @FloorSpace as int
DECLARE @RTB as bit
DECLARE @RTA as bit
--Variable used for Scope_Identity
DECLARE @ID as int
DECLARE Asset_Cursor CURSOR FOR
--For each rown that is returned in the below select statement will be imported into each table that is called in the below cursor.
SELECT a.UPRN
,at.AssetTypeId
,a.Description
,ad.geolocation
,CONVERT (date,a.HandoverDate) as HandoverDate
,CONVERT(date,a.ConstructionDate) as ConstructionDate
,l1.LookupReference as LocalAuthority
,l2.LookupReference as County
,a.Base1999Valuation
,a.NoOfBeds
,l3.LookupReference as ClassificationType
,l4.LookupReference as CategoryType
,ir1.EntityID as [address]
,sl1.LookupReference as Country
,a.FloorSpace
,a.RTB
,a.RTA
FROM DataLoad..Asset a
left join AssetType at on at.Description = a.AssetType
left join DataLoad..Address ad on ad.addressid = a.AddressID
left join Lookup l1 on l1.[Description] = a.[LocalAuthority] and l1.LookupTypeId = 55
left join Lookup l2 on l2.[Description] = ad.[County] and l2.LookupTypeId = 64
left join Lookup l3 on l3.[Description] = a.[ClassificationType] and l3.LookupTypeId=22
left join Lookup l4 on l4.[Description] = a.[CategoryType] and l4.LookupTypeId = 23
left join SystemLookup sl1 on sl1.[Description] = ad.[Country] and sl1.LookupTypeId=127
left join ImportReference ir1 on ir1.SourceRef = a.AddressID and ir1.EntityType = 'Civica.Housing.Cx.General.Service.Entities.Address'
ORDER BY a.id
OPEN Asset_Cursor
FETCH NEXT FROM Asset_Cursor INTO @AssetReference,@AssetType,@AssetDescription,@GeoLocation ,@ActualHandOverDate ,@ConstructionDate ,@LocalAuthority ,@County,@Base1999Valuation,@NumberofBedrooms,@ClassificationType,@CategoryType ,@Address ,@Country,@FloorSpace,@RTB,@RTA
WHILE @@FETCH_STATUS = 0
BEGIN
--Populate the Asset table from DataLoad.
INSERT INTO Asset (AssetReference,AssetTypeId,AssetDescription,GeoLocation,EffectiveFromDate,ActualHandoverDate,ConstructionDate,LocalAuthorityId,CountyId,Base1999Valuation,NumberOfBedrooms,ClassificationTypeId,CategoryTypeId,AddressId,CountryId,FloorSpace,AvailableForRTB,AvailableForRTA)
VALUES (@AssetReference,@AssetType,@AssetDescription,@GeoLocation,CAST(GetDate() as date),@ActualHandOverDate,@ConstructionDate,@LocalAuthority,@County,@Base1999Valuation,@NumberofBedrooms,@ClassificationType,@CategoryType,@Address,@Country,@FloorSpace,@RTB,@RTA)
--Now the first table is populated get the ID in order to populate all other tables
SELECT @ID = SCOPE_IDENTITY()
--Populate the Import Referance Table with the Asset data
INSERT INTO ImportReference (SourceRef, EntityID, EntityType)
VALUES (@AssetReference,@ID, '??.Housing.Cx.Asset.Service.Domains.Asset')
-- Populate the AssetAddress Table with the relevant data
INSERT INTO AssetAddress (AssetId,AddressId,EffectiveFromDate)
VALUES (@id,@Address,CAST(GetDate() as date))
FETCH NEXT FROM Asset_Cursor INTO @AssetReference,@AssetType,@AssetDescription,@GeoLocation ,@ActualHandOverDate ,@ConstructionDate ,@LocalAuthority ,@County,@Base1999Valuation,@NumberofBedrooms,@ClassificationType,@CategoryType ,@Address ,@Country,@FloorSpace,@RTB,@RTA
END
CLOSE Asset_Cursor
DEALLOCATE Asset_Cursor
Now the help I require is surrounding populating my third table -
-- Populate the AssetAddress Table with the relevant data
INSERT INTO AssetAddress (AssetId,AddressId,EffectiveFromDate)
VALUES (@id,@Address,CAST(GetDate() as date))
The AddressID in this table (AssetAddress) is NOTNULL, so I only want to only populate this table if the @address variable I declared is not null.
I need to remove these otherwise the import with fail.
February 10, 2016 at 6:47 am
Just worked it out -
As the ASSET table is now populated - I can use the following
-- Populate the AssetAddress Table with the relevant data. Only if the AddressID is populated
INSERT INTO AssetAddress (AssetId,AddressId,EffectiveFromDate)
SELECT a.AssetID,a.AddressId,CAST(GetDate() as date) FROM Asset a WHERE a.AddressId IS NOT NULL
February 10, 2016 at 6:50 am
Or this?
-- Populate the AssetAddress Table with the relevant data
IF @Address IS NOT NULL
INSERT INTO AssetAddress ( AssetId, AddressId, EffectiveFromDate )
VALUES ( @id, @Address, CAST(GetDate() AS date) );
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply