EDIT: forget it - when I figure out how to post results I will try again!

You have a unique index, so you could use it.

INSERT YOURTABLE( blah,blah2,blah3 etc)

SELECT blah,blah2,blah3 etc

FROM YOUROTHERTABLE T1

LEFT OUTER JOIN YOURTABLE T2

ON T1.unique_index_part1=T2.unique_index_part1

AND T1.unique_index_part2=T2.unique_index_part2

AND T1.unique_index_part3=T2.unique_index_part3

WHERE T2.some_column_that_does_not_allow_nulls IS NULL

You can do it with a recursive CTE - without sample data to show you, you had better google that (actually just remembered that the MSDN entry for CTEs uses...

Derek Dongray (4/24/2009)

---

Bob Hovious (4/23/2009)

---

With respect to testing, and I apologize for not having done more, it seems there are at least three or four variables about the test data...

that is a line feed character. It makes me wonder how the data got that in.:)

Try updating the column by replacing char(10) in the data with ''

you could try converting the column to varbinary and checking the hex values to see what is really in there.

Check the collation on the database and column.

Try using

WHERE UPPER(myColumn) = UPPER('Whatever')

OR

WHERE myColumn = 'Whatever' COLLATE Latin1_General_CI_AS

...just to test if it is a case sensitive collation issue

Yes, because with that join you get all the records from the left (or base) table and can then...

Johann Montfort (4/14/2009)

---

HI All,

I have the following code at the moment

SELECT * FROM

(

SELECT vidID, vidDate, vidTitle, vidDesc, AddedBy, OnlyForMembers, ViewCount,

ROW_NUMBER() OVER...

Have you really got no indexes?

I can't imagine a set based approach working well without indexes when you have millions of records.

I agree - BAH! MDX is not T-SQL. I am going to tell my mummy!

Out of curiosity, I had a play with replace()....

using this stored proc:

CCREATE PROCEDURE #usp_print_lines

@text NVARCHAR(MAX)

AS

DECLARE @eol nvarchar(22)

SET @eol=N''' UNION ALL SELECT '''

SELECT @text= N'SELECT '''+REPLACE(REPLACE(REPLACE(@text,N'''',N''''''),CHAR(13) + CHAR(10),@eol),CHAR(10),@eol)+''''

EXEC(@text)

--- Return lines

I...

Peso, you seem to have got that spot on now - I have run through a few different sets of data from low to high population and it is quick...

Peso (4/13/2009)

---

I can spot the error in my algorithm now.

Record 80 and 81 are mutually exclusive, because they have no common columns populated so both records fulfill the criteria of...

Peso, really interesting take on it, but it doesn't seem to work on my test data - unless I...