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](50) 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_LOCATIONS;

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 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', 'N/A', 'N/A', GETUTCDATE(), GETUTCDATE(), source.CompanyCode)		
WHEN MATCHED THEN
	UPDATE SET
		Name = source.CompanyDescription,
		LastModifiedDate = GETUTCDATE();

WITH locations AS (
    SELECT
		TRIM(fm.FinalMileFacilityCode) as Code,  
        TRIM(fm.FinalMileFacilityDescription) as Name,
		TRIM(fm.Line1) as AddressLine1,
		TRIM(fm.Line2) as 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 ON TRIM(o.ExternalCode) = fm.CompanyCode)

MERGE OrganisationRegistry.Locations target
USING (SELECT * 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',
		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 1

New Version Current

Key