Page Comparison

Jira Legacy

server	System JIRA
serverId	b157ffa3-650f-3eac-ac6a-49d6a4f1db87
key	WG-145

Final mile facilities are provided as a Excel file and should be imported to a WhiteGlove OrganisationRegistry database. The below is a spet-by-step instruction how to do this:

Create an intermediate SQL table (ensure the order of columns in Excel and the table is the same):

Code Block

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FinalMileFacilityImport]') AND type in (N'U'))
DROP TABLE [dbo].[FinalMileFacilityImport]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FinalMileFacilityImport](
	[CompanyCode] [nvarchar](100) NULL,
	[CompanyDescription] [nvarchar](100) NULL,
	[FinalMileFacilityCode] [nvarchar](50) NULL,
	[FinalMileFacilityDescription] [nvarchar](10050) NULL,
	[Title] [nvarchar](100) NULL,
	[FirstName] [nvarchar](100) NULL,
	[LastName] [nvarchar](100) NULL,
	[Line1] [nvarchar](100) NULL,
	[Line2] [nvarchar](100) NULL,
	[Line3] [nvarchar](100) NULL,
	[City] [nvarchar](100) NULL,
	[Country] [nvarchar](100) NULL,
	[PostalZip] [nvarchar](100) NULL,
	[CountryCode] [nvarchar](100) NULL,
	[PhoneNumber] [nvarchar](100) NULL,
	[EmailAddress] [nvarchar](100) NULL,
) ON [PRIMARY]
GO

Go to Edit FinalMileFacilityImport table data mode in SSMS.
Copy data from the excel file an past it to the table.
1. Verify everything is copied well as multiline values may be pasted as a separate rows.

Run the following script:

Code Block

BEGIN TRAN LOAD_COMPANIES;

INSERT INTOLOCATIONS;

WITH organisations AS (
    SELECT TRIM(fm.CompanyCode) as CompanyCode,
           TRIM(fm.CompanyDescription) as CompanyDescription,
           ROW_NUMBER() OVER(PARTITION BY fm.CompanyCode order by CompanyCode) AS rank
      FROM FinalMileFacilityImport fm)

MERGE OrganisationRegistry.Organisations target
USING (SELECT * FROM organisations 
	CompanyCode as ExternalCode,
	CompanyCode as Name,
	WHERE rank = 1) AS source
ON source.CompanyCode = target.ExternalCode
WHEN NOT MATCHED THEN
    INSERT (ExternalCode, Name, Role, City, Country, CreatedDate, LastModifiedDate, Code)  
    VALUES (source.CompanyCode, source.CompanyDescription, 'WHITE_GLOVE_PARTNER' as Role, 	'N/A' as City, 	'N/A' as Country,
	GETUTCDATE(, GETUTCDATE(), GETUTCDATE(), source.CompanyCode)		
WHEN MATCHED THEN
	UPDATE SET
		Name = source.CompanyDescription,
		LastModifiedDate = GETUTCDATE();

WITH locations AS (
    SELECT
		TRIM(fm.FinalMileFacilityCode) as CreatedDateCode,
	GETUTCDATE(  
        TRIM(fm.FinalMileFacilityDescription) as LastModifiedDateName,
	NULL		TRIM(fm.Line1) as DisabledDateAddressLine1,
	CompanyCode		TRIM(fm.Line2) as Code
FROM dbo.FinalMileFacilityImport fm
WHERE NOT EXISTS (SELECT 1 FROM OrganisationRegistry.Organisations o where fm.CompanyCode = o.ExternalCode)
GROUP BY CompanyCode

UPDATE OrganisationRegistry.Organisations SET Name = fm.CompanyDescription
FROM dbo.FinalMileFacilityImport fm
INNER AddressLine2,
		TRIM(fm.Line3) as AddressLine3,
		TRIM(fm.City) as City,
		TRIM(PostalZip) as Postcode,
		c.CountryId as CountryId,
		o.OrganisationId as OrganisationId
      FROM FinalMileFacilityImport fm
	  LEFT JOIN OrganisationRegistry.Countries c ON TRIM(c.Code) = fm.CountryCode
	  LEFT JOIN OrganisationRegistry.Organisations o onON TRIM(o.ExternalCode) = fm.CompanyCode)

INSERT INTOMERGE OrganisationRegistry.Locations target
USING (SELECT
FinalMileFacilityCode as Code,
FinalMileFacilityCode as ExternalCode,
FinalMileFacilityDescription as Name,
 * FROM locations) as source
ON source.Code = target.ExternalCode and source.OrganisationId = target.OrganisationId
WHEN NOT MATCHED THEN
    INSERT (
		Code,
		ExternalCode,
		Name,
		Type,
		AddressLine1,
		AddressLine2,
		AddressLine3,
		City,
		Postcode,
		OrganisationId,
		CreatedDate,
		LastModifiedDate,
		CountryId)  
    VALUES (
		source.Code,
		source.Code,
		source.Name,
		'FINAL_MILE_FACILITY' as Type,
Line1 as AddressLine1,
Line2 as AddressLine2,
Line3 as AddressLine3,
fm.City as City,
NULL as Region,
PostalZip as postcode,
CountryCode as CountryCode,
o.OrganisationId as OrganisationId,
GETUTCDATE() as CreatedDate,
GETUTCDATE() as LastUpdatedDate,
NULL as DisabledDate
FROM dbo.FinalMileFacilityImport fm
LEFT JOIN OrganisationRegistry.Organisations o on o.ExternalCode = fm.CompanyCode
WHERE NOT EXISTS (SELECT 1 FROM OrganisationRegistry.Locations l where l.ExternalCode = fm.FinalMileFacilityCode)

COMMIT TRAN LOAD_COMPANIES;,
		source.AddressLine1,
		source.AddressLine2,
		source.AddressLine3,
		source.City,
		source.Postcode,
		source.OrganisationId,
		GETUTCDATE(),
		GETUTCDATE(),
		source.CountryId)		
WHEN MATCHED THEN
	UPDATE SET
		Name = source.Name,
		AddressLine1 = source.AddressLine1,
		AddressLine2 = source.AddressLine2,
		AddressLine3 = source.AddressLine3,
		City = source.City,
		Postcode = source.Postcode,
		LastModifiedDate = GETUTCDATE(),
		CountryId = source.CountryId;

COMMIT TRAN LOAD_LOCATIONS;

5. Run the script to export existing locations:

Code Block

SELECT        l.LocationId, l.Code, l.ExternalCode, l.Name, l.Type, l.AddressLine1, l.AddressLine2, l.AddressLine3, l.City, l.Region, l.Postcode, o.Code AS Expr1, l.CreatedDate, l.LastModifiedDate, l.DisableDate, 
                         c.Name AS Expr2
FROM            OrganisationRegistry.Locations AS l LEFT OUTER JOIN
                         OrganisationRegistry.Organisations AS o ON o.OrganisationId = l.OrganisationId LEFT OUTER JOIN
                         OrganisationRegistry.Countries AS c ON c.CountryId = l.CountryId

Versions Compared

Old Version 2

New Version Current

Key