Duplicates removal - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicates removal

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-06-09 : 07:50:50

I have a tableA with ColA,colB,ColC and ColD
and ColC is a foreign key in table A which has duplicates

I have to remove the duplicates from this column C in table A and load the data to tableB where without duplicates

Can someone please help me out

I have tried differant sql an nothing is helping

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-06-09 : 07:52:13

can u post some sample data and requried output........

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-06-09 : 08:28:04

I dont have sample data to post but the scenario is exactly what i described

Iam trying to remove the duplicates from table A colC and load the same data to Table B.

ColC which is a foreign key in table A is primary key in table B which would not allow duplicates

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-09 : 08:44:36

This works in SQL Server 2008 and later. It will remove all duplicate records over ColC column from TableA and insert all TableA deleted records into TableB.

INSERT	TableB
	(
		ColA,
		ColB,
		ColC,
		ColD
	)
SELECT  ColA,
	ColB,
	ColC,
	ColD
FROM	(
		DELETE	f
		OUTPUT	deleted.ColA,
			deleted.ColB,
			deleted.ColC,
			deleted.ColD
		FROM	(
				SELECT	ColA,
					ColB,
					ColC,
					ColD,
					ROW_NUMBER() OVER (PARTITION BY ColC ORDER BY ColA DESC) AS RecID
				FROM	TableA
			) AS f
		WHERE	RecID > 1
	) AS d

N 56°04'39.26"
E 12°55'05.63"

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-06-09 : 09:28:26

I have two syntax errors
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'DELETE'.

and the other is Msg 102, Level 15, State 1, Line 20

which is at AS D

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-09 : 10:18:15

Well, do you have SQL Server 2008 as I wrote in the first sentence?

N 56°04'39.26"
E 12°55'05.63"

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-06-09 : 11:10:25

quote:
Originally posted by jim_jim

Iam trying to remove the duplicates from table A colC and load the same data to Table B.

ColC which is a foreign key in table A is primary key in table B which would not allow duplicates

If ColC is a foreign key in tableA and a primary key in tableB... then all values in ColC in tableA should exist in tableB already and trying to enter that will create dupes...

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-10 : 03:30:02

First Approach
----------------------------------------------------------------------------------------------
Will be creating a Column in the existing table for identity and then dump required data to Hash table and again insert back to both the table A & B

Create Table TableA ( COLA INT , COLB INT , COLC INT , COLD INT)

ALTER TABLE TableA ADD ID INT IDENTITY

Create Table #TableA ( COLA INT , COLB INT , COLC INT , COLD INT)
INSERT INTO #TableA
SELECT COLA , COLB , COLC , COLD FROM TableA WHERE ID IN (
SELECT
MAX(ID) AS ID FROM TableA
GROUP BY
COLC )
DELETE FROM TableA
INSERT INTO TableA
SELECT * FROM #TableA

INSERT INTO TableB
SELECT * FROM #TableA
----------------------------------------------------------------------------------------------

Second Approach
----------------------------------------------------------------------------------------------
Dump data to Hash table and then insert back to required tables
Create Table #TableA ( COLA INT , COLB INT , COLC INT , COLD INT, ID INT IDENTITY )

INSERT INTO #TableA
SELECT COLA , COLB , COLC , COLD FROM TableA

DELETE FROM TableA
INSERT INTO TableA
SELECT COLA , COLB , COLC , COLD FROM #TableA WHERE ID IN (
SELECT
MAX(ID) AS ID FROM #TableA
GROUP BY
COLC )

INSERT INTO TableB
SELECT * FROM TableA
----------------------------------------------------------------------------------------------

let me if any issues.

Lets unLearn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources