One option (of many) is to simply adapt the above (by adding a distinct and removing the ids):

declare @t table (id int identity(1, 1), v varchar(50))

insert @t

...

A few alternatives (just for fun)...

--row_number() version [generally the preferred approach, I would imagine]

; with t1 as (

select *, row_number() over (partition by ProductID order...

This makes it sound like you have a different requirement, since the original poster was asking for non-alphanumeric data. Maybe you want something like this?

--Sample...

This?

declare @SOURCE table (membershipid varchar(100), membername varchar(100), membershiptype varchar(100))

insert @SOURCE

select 424400000000, 'Andre Sim', 'Premium'

union all select 424400010000, 'Hello',...

Similar question (and answer) here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130994

Recommended article here: http://www.sommarskog.se/dynamic_sql.html

Here's my first stab in the meanwhile...

; with

t1 as (SELECT *, row_number() over (order by start_id) drow FROM @destination_scenario1)

, t2 as (select id, max(drow) as drow from @source_scenario1...

Can you explain why...

1. -700 -600 is not inserted into @destination_scenario1?

2. -400 -260 and -260 -120 are not expected outputs for scenario 2

Thanks 🙂

Here's some stuff to play around with, should you wish...

--structure

create table dbo.Myversionbatchlog (id int, versionsxml xml)

--/

--data

insert dbo.Myversionbatchlog

select 11, '<xml><version rowid="1"...

Maybe it's the table variable. Can you eliminate it with something along these lines?

declare @versionbatchlog table (id int, versionsxml xml)

insert @versionbatchlog

select 11, '<xml><version rowid="1" alias="hello world" />

...

There's an old article on it on this site...

http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/dedupingdatainsqlserver2005/2260/

This option became available with SQL Server 2005.

Jim C-203340 (11/30/2009)

---

The row_number() method is by far the quickest and cleanest method. If you've...

unladenswallow (4/28/2009)

---

If it's important to leave the decimal part alone, it's easy to just do this to the characters to the left of the decimal point in the original string,...

Good catch, GSquared - I was careless with...

Thanks Frédéric 🙂

I dare say that's my first ever 'publication' in a foreign language - great stuff!

I guess you could create a view which is the union of all your tables, and then update the view.

Having many similarly named tables is usually an indication that a...

Here's something similar for comparison, making use of powers of 2 rather than LIKE, and XML rather than string manuipulation.

--preparation

IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL DROP TABLE...