ben.brugman (8/3/2015)
This weekend I have been away from the keyboard. And as often the best idea's occure when away from the keyboard. E.g. on my bike (pushbike) or under the shower. This weekend I came up with the following:
select * into #D from D1Delete D1
insert into D1 select distinct * from #D
This works for most tables.
But does not work for tables which have an identity and not for tables with the larger datatypes like XML.
Maybe I should take more showers.;-)
Thanks all for you time and attention.
Ben
You can cancel the identity property by using a union or union all:
select top (0) * into #D from D1 union all select top (0) * from d1
insert into D1 select distinct * from #D
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.