It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value,...

My opinion, if it is a sequence or an identity really depends on a lot more than we can accurately guess from what was posted.  It might be that...

It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is...

It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is no longer...

An alternative is like below.  Which performs better depends on total data volume and index(es) present:

Edit: I'm at work so I have only a limited time to review all posts,...

• This reply was modified 5 years, 10 months ago by ScottPletcher.

You corrupted your data (at least for a relational model). You made it non-atomic, since that column now contains two pieces of info (type and id#), not one (just...

The full, unlimited Cartesian join will be huge overhead.  At least turn the match of the tables into a standard join:

select a.Ref_Tran_OU'Tran_OU', a.Ref_Tran_Type'Tran_Type', a.Ref_Tran_No'Tran_No', 'N', b.Remarks
from SampleTable...

D'OH, quite right, a copy/paste mishap I think.  I have corrected the original code.

OK, adjust it as you need to.  You didn't provide enough details to know which...

I completely agree with Celko.

At my place of work we had to import some employee data that came from 2 completely separate systems, one for temporary and one for...

Edit: Corrected typo, as pointed out by Mr. Brian Gale.

UPDATE D
SET MarketCompleted = 0, MarketAssigned = 0
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID,...

• This reply was modified 5 years, 10 months ago by ScottPletcher.

UPDATE dbo.table_name
SET data_column = REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), ''))
WHERE (LEN(data_column_name) < 12 OR LEN(data_column_name) = 13) AND
    (data_column LIKE '%*%')

REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), ''))

Some examples:

SELECT data_column,
    REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), '')) AS new_data
FROM ( VALUES('ABC*1234'),('BC*234'),('CDEFG*1'),('D*23456789012')...

Assuming the PKs are the clustering keys [*], for best performance you'd need to change the order of the keys in the Data table.

If you always (with perhaps only extremely...

Create a proc in the master db, with a name beginning with sp_, and you can directly run it against any user db. [If you're in Azure, nevermind, this won't...