Take a close look at your expected output for August 8th, and then tell me how and why it's supposed to be that way, considering the values you use for...

Yep.   Move the database to a different disk spindle.   If it's on a SAN, things get a tad more complicated, but then you'd have to work with your SAN Admin...

Case Sensitve collation ?

Without any of your code to look at, I have no idea what you'll need to do.   First order of business after you post your actual query, is to get...

You can wrap your OPENROWSET into an SSIS package and then either use a derived column transformation, or just set the necessary component settings to not fail that element due...

Okay, but you used the words "top level" without defining exactly what that means in your environment.   We can't even guess as you've provided no useful context.   Assuming you have...

It is starting to sound like a data problem.   I'd start tracking exactly which tables are involved and then looking at that data, to see if you can find anything...

You're looking for the functionality provided in Jeff Moden's string splitter named DelimitedSplit8K, and if you're data in NVARCHAR as opposed to VARCHAR, you'll want to get the NVARCHAR version...

Try something like this:CREATE TABLE #Table_A (
    ProductCode VARCHAR (255),
    FranchiseCode VARCHAR (255),
    FranchiseGroup VARCHAR (255),
    FranchiseValue VARCHAR (255)
);
INSERT INTO #Table_A (ProductCode, FranchiseCode, FranchiseGroup, FranchiseValue)
    VALUES    ('0001','aaaa','',''),
            ('0002','bbbb','',''),
            ('0003','','B1','Value1'),

You put an updlock on a table and then want to read from it in a separate transaction?   That just doesn't make any sense.   tablock, perhaps, but not updlock.