Page Comparison

...

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;

...

Versions Compared

Old Version 4

New Version Current

Key