Conversion failed when converting the nvarchar value to data type int while inserting records into table

I am using a script to automatically update a database daily to sync Azure Blob Storage with a queryable Azure SQL Server database. Among deleting and updating records, I will insert brand new records that meet a certain criteria. The following is an insert statement that I am working with currently.

The first record is inserted into the database without issue, however, the second record throws an error:

Conversion failed when converting the nvarchar value '[{"Date":"2019-06-17"},{"Index":"2"},{"PAN":"987"},{"Phase":"Construction"},{"Primary":"False"},{"Project ID":"3484"}]' to data type int.

The column that this nvarchar is referencing is defnied as a nvarchar(MAX), not an int.

Database schema: https://gyazo.com/b95367d03e71b6f1138111668bf56e05

Create a table...

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [BlobSync].[BlobInventory]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [AccountName] [nvarchar](max) NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    [CreationTime] [datetime] NOT NULL,
    [LastModified] [datetime] NOT NULL,
    [Etag] [nvarchar](max) NULL,
    [ContentLength] [bigint] NULL,
    [ContentType] [nvarchar](max) NULL,
    [ContentEncoding] [nvarchar](max) NULL,
    [ContentLanguage] [nvarchar](max) NULL,
    [BlobType] [nvarchar](max) NULL,
    [AccessTier] [nvarchar](max) NULL,
    [Metadata] [nvarchar](max) NULL,
    [Deleted] [bit] NULL,
    [RemainingRetentionDays] [int] NULL,
    [TagCount] [int] NULL,
    [Tags] [nvarchar](max) NULL,
    [LastUpdatedUTC] [datetime] NULL,

    CONSTRAINT [PK_BlobInventory] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
                      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

DROP TABLE IF EXISTS #TempTableInsert; 

SELECT * 
INTO #TempTableInsert 
FROM [BlobSync].[BlobInventory] 
WHERE [ID] < 0; 

INSERT INTO #TempTableInsert (AccountName, Name, CreationTime, LastModified, Etag, ContentLength, ContentType, ContentEncoding, ContentLanguage, BlobType, AccessTier, Metadata, Deleted, RemainingRetentionDays, Tags, TagCount, LastUpdatedUTC) 
VALUES 
(
'blobstoragename'
, 'project-images/bdb70f3e-8d8b-41f1-b357-66e1792280e9'
, '7/22/2024 2:28:44 PM'
, '7/23/2024 12:53:18 PM'
, '0x8DCAB597AF04A5D'
, 2380580
, 'image/avif'
, null
, null
, 'BlockBlob'
, 'Cool'
, '{"Description":"This is a description.","FileName":"20190611_120704.jpg"}'
, null
, null
, '[{"Date":"2019-06-17"},{"Index":"1"},{"PAN":"23567"},{"Phase":"Design"},{"Primary":"False"},{"Project ID":"3484"}]'
, 6
, null),
('blobstoragename'
, 'project-images/dd7f2b7e-a824-4ab9-992f-4eae791f8bc6'
, '7/22/2024 2:28:44 PM'
, '7/22/2024 2:28:45 PM'
, '0x8DCAA9DA602D97F'
, 1668161
, 'image/avif'
, null
, null
, 'BlockBlob'
, 'Cool'
, '{"Description":"TEST2","FileName":"20190611_120706.jpg"}'
, null
, null
, '[{"Date":"2019-06-17"},{"Index":"2"},{"PAN":"987"},{"Phase":"Construction"},{"Primary":"False"},{"Project ID":"3484"}]'
, 6
, null); 

INSERT INTO [BlobSync].[BlobInventory] 
    SELECT 
        AccountName, [Name], [CreationTime], [LastModified],
        [Etag], [ContentLength], [ContentType], [ContentEncoding],
        [ContentLanguage], [BlobType], [AccessTier], [Metadata],
        [Deleted], [RemainingRetentionDays], [Tags], [TagCount],
        [LastUpdatedUTC]
    FROM 
        #TempTableInsert 
    WHERE 
        (#TempTableInsert.[AccountName] + '/' + #TempTableInsert.[Name] NOT IN (SELECT [BlobInventory].[AccountName] + '/' + [BlobInventory].[Name] FROM [BlobSync].[BlobInventory]));

I have used this script before without error and it has worked, for an unknown reason this SQL code will not work in my C# script or when I use it in SSMS (For the second record).

Solution

The TagCount and Tags fields are reversed between the column list and the column data. So you're putting the value 6 into Tags and the long value [{"Date":"2019-0... into TagCount.

While I'm here, it is not correct to provide datetime values as SQL literals like this: 7/22/2024 2:28:44 PM. Different languages and cultures have their own expectations around how date and times are formatted. The SQL language is no different just because it's a programming language.

The value should be formatted in one of these two ways:

2024-07-22T14:28:44  (ie: yyyy-MM-ddTHH:mm:ss)
20240722 14:28:44  (ie: yyyyMMdd HH:mm:ss)

If you have only the date (no time), the only correct option looks like this:

20240722 (ie: yyyyMMdd - no seperators!)

Anything else is wrong, and leaves you open to potential invalid input or reading the wrong date.