with TS as( SELECT ROW_NUMBER() over (PARTITION By code order by code) as rowid,* FROM StateInfos ) select [Hexcode] ,[DisplayName] ,[Code] ,[OriginalCode] ,[IsPlaned] ,[Type] ,[IsStatic] ,[StateInfoGroupId] from TS where ts.rowid = 1
以下语句:过滤掉 StateInfos表重复数据,并且不插入Code重复的数据
with TS as( SELECT ROW_NUMBER() over (PARTITION By code order by code) as rowid,* FROM StateInfos ) INSERT INTO [dbo].[StateInfos2] ([Hexcode] ,[DisplayName] ,[Code] ,[OriginalCode] ,[IsPlaned] ,[Type] ,[IsStatic] ,[StateInfoGroupId]) select [Hexcode] ,[DisplayName] ,[Code] ,[OriginalCode] ,[IsPlaned] ,[Type] ,[IsStatic] ,[StateInfoGroupId] from TS where ts.rowid = 1 and not exists(select 1 from StateInfos2 where StateInfos2.Code = TS.Code)