create table test(id int,name varchar(30),descrptn varchar(30))
1.(it contain duplicate records)
select * from test
--here this table(test1) only keep unique records
select distinct * into test1 from test
--here drop the table test
drop table test
--here rename the table test1 to test
sp_rename test1,test
OR
2.(it contain duplicate records)
select * from test
--here this table(test1) only keep unique records
select distinct * into test1 from test
select * from test1
--here delete all the records of table(test) test
truncate table test
select * from test
--here insert one whole table(test1) records into another table(test)
insert into test select * from test1
select * from test
-- then we can drop the table(test1)
drop table test1
OR
insert into test select * from test
3.(it contain duplicate records)
select * from test
--select whole query given below and exucute,we will get unique records
with cte as(
select id,name,descrptn,RN = ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)
FROM test
)
DELETE FROM CTE WHERE RN > 1
1.(it contain duplicate records)
select * from test
--here this table(test1) only keep unique records
select distinct * into test1 from test
--here drop the table test
drop table test
--here rename the table test1 to test
sp_rename test1,test
OR
2.(it contain duplicate records)
select * from test
--here this table(test1) only keep unique records
select distinct * into test1 from test
select * from test1
--here delete all the records of table(test) test
truncate table test
select * from test
--here insert one whole table(test1) records into another table(test)
insert into test select * from test1
select * from test
-- then we can drop the table(test1)
drop table test1
OR
insert into test select * from test
3.(it contain duplicate records)
select * from test
--select whole query given below and exucute,we will get unique records
with cte as(
select id,name,descrptn,RN = ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)
FROM test
)
DELETE FROM CTE WHERE RN > 1
No comments:
Post a Comment