Page Comparison

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](100) 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.

Run the following script:

Code Block

BEGIN TRAN LOAD_COMPANIES;

INSERT INTO OrganisationRegistry.Organisations
SELECT 
	CompanyCode as ExternalCode,
	CompanyCode as Name,
	'WHITE_GLOVE_PARTNER' as Role,
	'N/A' as City,
	'N/A' as Country,
	GETUTCDATE() as CreatedDate,
	GETUTCDATE() as LastModifiedDate,
	NULL as DisabledDate,
	CompanyCode 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 JOIN OrganisationRegistry.Organisations o on o.ExternalCode = fm.CompanyCode

INSERT INTO OrganisationRegistry.Locations
SELECT
FinalMileFacilityCode as Code,
FinalMileFacilityCode as ExternalCode,
FinalMileFacilityDescription as 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;

Versions Compared

Old Version 1

New Version 2

Key