Trying to make it on top

There is no other way to sort the records other than using 'Priority' column as well as 'EffDate' column. Business rule will select highest priority column for calculation where the...

The last link gave me a better idea. Extended transaction is what caught my mind. I will think about it again.

But what are those advantages of foreign key constraint, I still be able to maintain data integrity through trigger. There are cases that business codes only contains two characters.

I prefer to change it to varchar(5), so that i can avoid the padding space and usage of trim everywhere. Yes, it is not a big deal, but if nothing...

I do not know how to explain the business in more sensible manner, but please consider that the original table here have few more fields which are used for business,...

I do understand the concern here. But there is nothing wrong in inserting a new record with high priority as per business. The details I provided is an example. In...

That solution works well.

Thank you.

Thanks to Igor and Kingston for their help.

The script works fine.

Thank you so much Kingston... that works. I will be implementing your logic in my original script. I hope using CTE for millions of data is okay. Let you know...

Sorry for the trouble. I hope below code will give better idea.

create table ##Temp1 (

mytab1col1 int,

mytab1col2 varchar(10),

mytab1col3 smalldatetime)

insert into ##Temp1 (mytab1col1, mytab1col2, mytab1col3)

select 1, 'name1', '2010-01-01'

union all

select 1, 'name2', '2010-01-01'

union...

That's true and it is because there was below mismatch with respect to Id 4.

##Temp1 contains

select 1, 'name1', '2010-01-01'

##Temp2 contains

select 4, 'name1', '2010-01-02' --> The eighth record in original script:...

That query is having a problem, lets say the records with id value 4 in column mytab1col1 doesn't have any mismatch. The result still displays id 4, which it should...

Since I have different ID's and I need them to be displayed in result and I don't want to compare the ID's but all other columns, EXCEPT will not work.

Thank you.... this works