I'm assuming, and hoping, that you copied the files to the new locations rather than moved them (never do that, always copy and retain the originals until the ONLINE works...

The only thing I see that could help would be a separate index on just:
(col1, col2)
If possible, use compression on the index, at least row but page if...

I think you could use an INSTEAD OF INSERT trigger to do that, adding a view if necessary to avoid SQL "compile"/pre-scan issues.  Of course the trigger would apply to...

I don't follow.
So there can be multiple rows for Product ABC123.  Why?  To allow changes over time?  Or for diff locations?

It might be helpful to do this logically,...

If you want to add an IDENTITY column in table1 that is not in table2, you can do this:

select IDENTITY(int, 1, 1) AS id, *
into table1...

SELECT EName, UnitsSold, Category
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Category ORDER BY UnitsSold DESC) AS row_num
    FROM #t
    WHERE...

You should do at least some normalization here.  I'd say the below is the minimum.  Easiest is a separate row for each column.  Without more normalization, yes, that will result...

You could change the date format just for that code:

DECLARE @dateformat_setting char(3)
IF OBJECT_ID('tempdb.dbo.#useroptions') IS NOT NULL
  DROP TABLE #useroptions
CREATE TABLE #useroptions (

Sorry, extremely busy at work, didn't finish code before.  Maybe as below?

INSERT INTO [dbo].[SomeCause] ( SomeID, SomeType, SomeThingElseID, SomeDesc )
SELECT SC.SomeID, SC.SomeType, SC.SomeThingElseID_New, SC.SomeDesc

If those values truly correspond with your results:
741:51:59 = Jan 30 1900 11:34PM
then I'm not sure what you can, as I don't see a clear link between the two...

You didn't provide enough data to know that you want to INSERT, but you likely want something along these lines:

INSERT INTO [dbo].[SomeCause] ( SomeID, SomeType,...


exec('declare @testvar nvarchar(128); select top (1) @testvar = NameSchema from #TempCommonMatchFormatted; select @testvar as testvar')

If you want to return a value(s) to a...

The code looks good to me.  I think it's better to test row existence outside the TRY.

You use XACT_STATE() to:
(1) avoid attempting a ROLLBACK or COMMIT when a trans is not active
(2) to know whether to do a ROLLBACK or COMMIT in certain cases (if...