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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".