Yep, you're right...didn't catch that subtlety in the requirements. This should do the trick:

select yr,

month,

member,

(select count(distinct code)

from #testtable t3

where t1.yr = t3.yr

and...

I believe all you need is a delete with an EXISTS:

delete a

from tableA a

where exists (select 't'

from tableB b

where a.Name= b.Name)

Let me know if that works.

SB

There are several problems here. First, we don't have the structure of my_table. I'm assuming in the code below that it includes 3 columns and that you want...

Jeff, are you referring to the IGNORE_DUP_KEY option?

By "inserting in batches" do you mean breaking up the insert into batches? Such as doing 10,000 rows at a time instead of doing all 1 billion rows at...

Try this: (I'm using the test data posted by Jeff Moden earlier...tks Jeff 🙂 )

selectyr,

month,

member,

count(*),

(select CASE

WHEN ROW_NUMBER() OVER (ORDER BY t1.yr, t1.month, t1.member) = 1...

Greetings,

It depends on 2 things:

1. Do you want to delete all the rows or just leave one of the duplicates in there?

2. If you just want to...

If you open up the table in Object Explorer and expand the columns, do they have the right names? (Be sure to right-click on the table and hit 'Refresh'...

aschoch: Finally got around to trying your CDATA suggestion, but it only prints out 8,192 characters. I think the key is to use the FOR XML clause. ...

That is interesting. Does this work for anyone else out there?

SB

mjarsaniya suggested that in a previous post, but I tried it and it only printed out 8,192 characters.

OK...I have fixed the bug and reposted the SQL as an update to this article. It's a very simple fix. Just remove the '+ 1' in Line 70...

Sorry for not replying sooner...

thakur_samir: The option you mentioned DOES affect the length of the string that is displayed, but it has a max value of 8192. Try...

Regarding the reply by aschoch: I'm not sure how your suggestion works...you mentioned that you ran this query:

SELECT CONVERT(xml, '') AS DataXML FROM MyTable

However, I don't see a column...

Referring to the last post about the XML FOR PATH functionality, this is very interesting, but it has one issue: when you have the "" characters in the string,...