Import SQL Server 2016 and Excel Data into SQL Server 2008 R2

Background

Development  

SQL Server 2016 (“2016”)
Database: LibDB
    Branch table: (prod data)
    Category table: (prod data)
    Program table: (fake data)

Production
SQL Server 2008 R2 (“R2”)
Excel Sheet: needs to be imported into Production’s Program table

Import 2016 Database into R2

Please note: R2 doesn’t recognized the database backup generated by 2016

Script to Create Database Schema (DB and All Table Structures)

2016 SMS/ LibDB/Tasks/Generate Scripts/ Next /Script entire database and all database objects/ Next /Advanced:

    Script for Server Version: SQL Server 2008 R2
    Types of data to script: Schema only

/OK

Run the script on R2

There will be errors in database creation when running this script on R2. Please replace the error lines with similar database creation statements that are generated on R2 itself.

Script to Insert Data for Branch and Category Table

2016 SMS/ LibDB/Tasks/Generate Scripts/next/Select specific database objects/Check Branch and Category table/ Next /Advanced/ Types of data to script: data only/OK

Run the script on R2.

Import Excel Data into R2

Import Excel Sheet into a New R2 Table

R2 SMS/ LibDB/Tasks/Import Data/Next/Choose a Data Source:

    Data source: Microsoft Excel
    Excel file path: c:\folder\Program.xlsx
    Excel Version: Microsoft Excel 2007
    Check First row has column names

/Next/Choose a Destination:

    Destination: SQL Server Native Client 11.0
    Server name: LibServer
    Authentication; Use Windows Authentication
    Database: LibDB

/Next/Copy data from one or more tables or views/Next/Change Destination table name to Imported$/If necessary, Edit Mappings/In Destination column, select “ignore” for unwanted columns/Next/Run immediately

SQL scripts for Validation of the Imported Excel Data 

To ensure each row’s Branch name in Imported$ table (FK) has a matched Branch name in Branch table (PK), please check:

USE LibDB
GO

SELECT * FROM Imported$ AS x
WHERE NOT EXISTS
(
SELECT * FROM Branch as b WHERE b.Name LIKE '%' + LTRIM(RTRIM(x.Branch)) + '%'
)

To ensure each row’s Category name in Imported$ table (FK) has a matched Category name in Category table (PK), please check:

/* Note: all special chars contained in Category are stripped before comparison. We compare text only */

USE LibDB
GO

/* https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server */

DROP FUNCTION [dbo].[StripCharacters]
GO

CREATE FUNCTION [dbo].[StripCharacters]
(
    @String NVARCHAR(MAX),
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END
GO

SELECT * FROM Imported$ AS x
WHERE NOT EXISTS
(
SELECT * FROM Category as c WHERE dbo.StripCharacters(c.Name, '^a-z0-9') LIKE '%' + dbo.StripCharacters(x.Tag, '^a-z0-9') + '%'
)

SQL scripts for Insertion of the Validated Excel Data

USE LibPrograms

DROP FUNCTION dbo.MakeDate
GO

/* MakeDate(2017, “Jan”, 30) returne “2017-1-30” */

CREATE FUNCTION dbo.MakeDate
(
@year int,
@month VARCHAR(250),
@day int
)
RETURNS date
AS
BEGIN
DECLARE @intMonth int
SET @intMonth = CASE
WHEN @month LIKE 'Jan%' THEN 1
WHEN @month LIKE 'Feb%' THEN 2
WHEN @month LIKE 'Mar%' THEN 3
WHEN @month LIKE 'Apr%' THEN 4
WHEN @month LIKE 'May%' THEN 5
WHEN @month LIKE 'Jun%' THEN 6
WHEN @month LIKE 'Jul%' THEN 7
WHEN @month LIKE 'Aug%' THEN 8
WHEN @month LIKE 'Sep%' THEN 9
WHEN @month LIKE 'Oct%' THEN 10
WHEN @month LIKE 'Nov%' THEN 11
WHEN @month LIKE 'Dec%' THEN 12
ELSE 1
END
RETURN CONVERT(VARCHAR, @year) + '-' + CONVERT(VARCHAR, @intMonth) + '-' + CONVERT(VARCHAR, @day)
END
GO

DELETE FROM Program
GO

INSERT INTO Program(Adult, BranchId, CategoryId, Child, Initial, Name, Young, date)
SELECT x.Adult, b.Id, c.Id, x.Child, x.Initials, x.Program, x.YA, dbo.MakeDate(2017, x.Month, x.Day)
FROM Imported$ AS x
JOIN Branch AS b
ON b.Name LIKE '%' + x.Branch + '%'
JOIN Category AS c
ON dbo.StripCharacters(c.Name, '^a-z0-9') LIKE '%' + dbo.StripCharacters(x.Tag, '^a-z0-9') + '%'