If you haven't already created an index on your temporary table it might speed processing up if you add one before doing this update:

CREATE INDEX IX_#party_id_1 ON...

Jonathan/Thom, You both are right actually. We have many scripts with dates in dd/mm/yyyy and yyyy-mm-dd format. Plus we have to change few staff members login to English login...

As you discovered, you can't index a View that has a UNION in it. In truth, if you need this type of design it does indicate a design flaw;...

Languages are there for a reason, and they do have impacts on  "simple" things such as the date here. The important part is that you code appropriately and therefore...

Try this:

set language french;
use tempdb;
declare...

Is that really the solution? Applications, and code, should be...

Jonathan, Is this a change in SQL2016? Because the format yyyy-mm-dd behaves perfectly fine in SQL 2008 R2. I ran your set of correct/incorrect ones and the output changes...

Just use a custom function as a constraint:

CREATE FUNCTION dbo.chkTestSplitOwnership (@Item int, @Owner char(1))
RETURNS int
AS 
BEGIN
  DECLARE @retval int=0
  SELECT @retval =1
   WHERE...

Use '20180801' instead of '2018-08-01'

If the columns on your table were datetime2 then it would also correctly convert the format you are using.

set language british

/* CORRECT CONVERSION...

I've done this but not the way you want.  It's super simple, though.  Create another database, move your large table to that, and create a synonym in the original...

Just replace TestTable with your table name.

DECLARE @TABLE_NAME sysname
SET @TABLE_NAME = 'TestTable'
SELECT @TABLE_NAME,
       STUFF((SELECT ', ' + COLUMN_NAME
   ...

• This reply was modified 6 years, 3 months ago by Jonathan AC Roberts. Reason: Added more general query

There weren't any duplicates in the results of the code I supplied.

Do you want to be more specific, maybe say what didn't work?

You really should read this before you post a question How to post data/code on a forum to get the best help

Here is a script that will set up...

Another way to do it (it's just a modified version of ScottPlecher's good method):

;WITH CTE AS 
(
    SELECT * 
   ...