Still there is a shorter version:

select * 
from leftTable  T1
join rightTable T2 
   on (
     ...

SELECT CONVERT(varchar(10), I.InvoiceDate, 110) AS InvoiceDate

Still there is a shorter version:

select * 
from leftTable  T1
join rightTable T2 
   on (
      T1.C1 =...

You never want to use ISNULL() in a WHERE or JOIN clause.  The code may be slightly longer the other way, but it could potentially run much faster.

ALTER SCHEMA [dbo] TRANSFER HWData.DesktopSoft_Master;

Then your original JOIN is fine and likely the cleanest way to do the join.

So you want a NULL in the column to match *every* column on the other table that isn't NULL?  I.e., NULL is like a wildcard match?

I would think you'd want this:

T1 join T2

on ((T1.C1 = T2.C1) OR (T1.C1 IS NULL AND T2.C1 IS NULL))

and ((T1.C2 = T2.C2) OR (T1.C2 IS NULL AND T2.C2 IS NULL))

How critical is your application?

You will have an outage between steps 2 and 3 and if the table has foreign key constraints you cannot use truncate table

Don't forget to...

I did include TRUNCATE in the trans, from the start.  Not exactly sure how else you want the transaction structured.

I can understand SQL having to scan the table / index, but I don't see why SQL would need to do a sort.  Btw, an asc index will do, you...

Yes, technically TABLOCKX is only for that statement.  I guess in theory someone could INSERT a row between the SELECT and the TRUNCATE.

I should have added HOLDLOCK to the first...

I have to admit, in this situation I'd likely just use a cursor and a loop, something like below, just because of the complexity, and likely overhead, of recursion in...

Something along these lines:

SELECT TOP (0) *
INTO dbo.maintable_backup
FROM dbo.maintable
--Edit: Added UNION ALL to cancel IDENTITY property, if present
UNION ALL
SELECT TOP (0) *
FROM dbo.maintable

BEGIN TRANSACTION
BEGIN TRY
INSERT INTO dbo.maintable_backup
SELECT...

• This reply was modified 5 years, 4 months ago by ScottPletcher. Reason: Added code to table create, using INTO, to cancel IDENTITY property if there is an IDENTITY column in the table

The max list is easy enough to produce.  Sorry, I don't fully understand the 'OK' part well enough yet to add that to the query.  'OK' just meaning that you...