Duplication Check - 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

Data Corruption Issues

Duplication Check

Author

Topic

CMT
Starting Member

3 Posts

Posted - 2008-08-12 : 11:22:52

Hi Everyone

I have a table which will merge 1 record with another by looking at 2 columns and 1 has been set to be the record that is to be kept.

However i need to check all rows on the column which is to be merged with column that is to be kept to see if the value has not been entered on another row and if so move this row to another table (REJECT), no trace of this record should exist in the original merge table.

For example:

ColumnA(Keep) ColumnB(Merge)
A B
A C
D A

As you can see on the third row value D will be kept and will have value A merged, however this has been stated previously that it will be kept and values B & C will be merged with value A. In this case i need the third row to be moved to another table (REJECT), so should end up looking like:

ColumnA(Keep) ColumnB(Merge)
A B
A C

Before the merge takes place.

Some advise would be helpful, in how this can be done.

Regards

Chris

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 11:37:06

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

CMT
Starting Member

3 Posts

Posted - 2008-08-13 : 03:31:46

Hi Peso

SQL 2000, although we will be moving to SQL 2005 in the near future.

Chris

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 03:43:35

For SQL Server 2000, try this

DECLARE	@Original TABLE (Keep CHAR(1), Merge CHAR(1))

INSERT	@Original
SELECT	'A', 'B' UNION ALL
SELECT	'A', 'C' UNION ALL
SELECT	'D', 'A'

SELECT	*
FROM	@Original

DECLARE	@Reject TABLE (Keep CHAR(1), Merge CHAR(1))

INSERT	@Reject
SELECT	o1.Keep,
	o1.Merge
FROM	@Original AS o1
WHERE	EXISTS (SELECT * FROM @Original AS o2 WHERE o2.Keep = o1.Merge)

DELETE		o
FROM		@Original AS o
INNER JOIN	@Reject AS r ON r.Keep = o.Keep
			AND r.Merge = o.Merge

SELECT	*
FROM	@Original

SELECT	*
FROM	@Reject

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 03:45:06

For SQL Server 2005, try this

DECLARE	@Original TABLE (Keep CHAR(1), Merge CHAR(1))

INSERT	@Original
SELECT	'A', 'B' UNION ALL
SELECT	'A', 'C' UNION ALL
SELECT	'D', 'A'

SELECT	*
FROM	@Original

DECLARE	@Reject TABLE (Keep CHAR(1), Merge CHAR(1))

DELETE		o1
OUTPUT		deleted.Keep,
		deleted.Merge
INTO		@Reject
FROM		@Original AS o1
INNER JOIN	@Original AS o2 ON o2.Keep = o1.Merge

SELECT	*
FROM	@Original

SELECT	*
FROM	@Reject

E 12°55'05.25"
N 56°04'39.16"

CMT
Starting Member

3 Posts

Posted - 2008-08-13 : 10:10:58

Thanks Peso thats helped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources