There are so many flaws in the design and implementation that this should earn a failing grade.
Why use unicode sometimes?
Many of the data types are deprecated.
Never use...

Hmm, seems like a serious security hole to me, granting database access to a login just because a username matches a login name.  I would only manually fix up a...

You can get the statistics updated dates on all objects.


SELECT name AS index_name, STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes


Interesting, I took a very different approach when I wrote this similar function years ago.

CREATE FUNCTION dbo.DateTime2Precision (@dt datetime2(7), @precision varchar(7))
    returns datetime2

SELECT t.name, convert(varchar(6),t.create_date,112) AS MyDateCol FROM sys.tables t;

It is incorrect to talk about a format for a date column itself.  A date is always stored in a binary form with no display format associated with it.

It all comes down to the rules and boundaries of your project world.

Are there only five tables to be synchronized?
Are the five tables constrained by foreign keys...so...

I'm not a fan of Sync, especially since it is a twilight product.  Syncing is a seriously difficult task to do.  There will be merge conflicts that need resolved manually. ...

I notice your input file has headings in the first line.  Are you skipping over the first line in your processing?  If not, edit the input file to remove the...

A varchar in the format of ccyymmdd is a very safe and easy thing to convert straight to a date so I use it often.

DECLARE...

If you have data containing single or double quotes, it will fail in dynamic sql while clean data will pass.

If you are generating dynamic sql it will depend...

The default server connection setting and the default database setting are irrelevant if you're putting SET QUOTED_IDENTIFIER ON in the script you are running.  Are you generating dynamic SQL in...

It is a horrible technique but gets the job done.  It loops through each row and concatenates it to the variable.  A simplified line of code would be SET

Here is a wild guess

DECLARE @t table (d date not null, n int not NULL);

INSERT INTO @t values ('05/09/2017', 88688), ('12/28/2016',66777), ('10/14/2016',55444)

I stopped using queue tables eight years ago when 2008R2 came out with filtered indexes (like dBase had 30 years ago).  Let the filtered index be your processing queue.  By...