you are right that its not HUGE, but as you know its a relative term. I can not purge the database ever or even take the 'old' data offline/archive.

database...

thanks for your input, it did help of course.

U are right in regards to database design, it was not designed but built on need basis long time ago. there are...

sorry if i was not clear enough.

there is more than one table , about 20 with average number of colums in each tabel about 300

each table have same PK  called...

thanks so much for your help and insight,, i appreciate it very much,, thanks

yes,, this gives me thousands of rows if i dont try to create index,, so i know temp is created

--CREATE INDEX [IndexTMP] ON [tempdb].[#tmpTBL]() WITH FILLFACTOR = 70...

getting this error

Cannot create an index on '#tmpTBL', because this table does not exist in database 'xyz'.

how would i do that,,,,, i only need to do it ONCE ,,thank God

this seem to work ,, thanks,, i tried to run it on the actual data -- both tabels have aorund 100K rows ,, and its still running after 15 mins...

here is the data that would be a problem with your suggestion --- design change is not in my hands so

key ...

these values are patient diagnosis codes i.e upto 4 of em ,, order is irrelevant ,, so x1 is not related to y1 or y2 etc.. these are just diagosis...

this works fine except the case i pointed out earlier ,, use these two as i have in your solution and c what i mean

INSERT @F

SELECT 1, 12, 1, 2,...

ok,, let me restate the issue

table F has most recent and correct data and can not be overwritten with values from table D -- most of the these records have...

one thing i mentioned in my initial post was that --- i cant overwrite any data in table F ,, so if X1 is not null then i dont...

there is one problem with this ,, look at this case

INSERT @F

SELECT 1, 12, 1, 2, null,null UNION ALL

INSERT @D

SELECT 1, 9, 1, 2, null,null UNION ALL

do we not lose...

i intentionaly did not get into 1-many problem ,, but there is another column in tableY called visitnumber

so using your tableY it will look like this

Fkey y1  y2  y3  y4 ...