Declare @TAble TAble 
(
  [ID] int, 
  [Name] varchar(10),
  [Address] varchar(100),
  [Telephone] varchar(100),
  [Image] varchar(10)
)

INsert @Table 
Select 1, 'adam', '45 tree gate road', '00000 000000', 'adam.jpg' Union All 
Select 2, 'uno', '44 garstang road', '00001 000001', 'uno.jpg' Union All 
Select 3, 'adam', '45 tree gate road', '00000 000000', 'adam.jpg'Union All
Select 4, 'brian', 'belgarth house', '00022 000022', 'adam.jpg' Union All
Select 5, 'karen','3 chester close', '00002 000002', 'adam.jpg' Union All
Select 6, 'uno','44 garstang road', '00001 000001', 'uno.jpg' Union All
Select 7, 'adam','45 tree gate road', '00000 000000', 'adam.jpg' 



Delete From @Table Where ID Not IN 
(
	Select [ID] From @Table t1 Where [ID] In 
	(
		Select Min([ID]) From @Table t2 Where t1.[Name] = t2.[Name] And t1.[Address]  = t2.[Address] 
		And t1.[Telephone] = t2.[Telephone] And t1.[Image] = t2.[Image]
	)
)

Select * From @Table 

quote:

---

Originally posted by abuhassan

are there any strategies that should be implemented at the data tier so as to prevent duplication of data?

---

quote:

---

Originally posted by eyechart
put a proper primary key on your table. Or add a unique key to your table on the columns that guarantee uniqueness.
-ec

---

quote:

---

Originally posted by chiragkhabaria


I guess he has the unique key, but other records are duplicated.

Chirag

---

quote:

---

Originally posted by abuhassan

Hi

I have a primary key but the problem was that the data was kept in various text files and when i import tha data i import the data into the view of the table using BCP to load the data from the text file to the table.

the data that comes in text files doesnt have a primary key.

hence i have duplicate records also some text files are exactly the same with different names hence the data is duplicated accidently and the same set of data is loaded twice.

is it possible to have a composite key when im using BCP will that detect dulicate records and avoid inserting them?

---

quote:

---

Originally posted by abuhassan

Currently no only one ID colunm as the primary key

---

ALTER  PROCEDURE spLoadHistory @path varchar(300) AS 
Declare @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT Cust..VW_CustHistoryLoad
FROM ''' + @path+ ''' 
WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

DELETE		t1
FROM		@Table t1
WHERE		t1.ID NOT IN	(
					SELECT		MAX(t2.ID)
					FROM		@Table t2
					GROUP BY	t2.Name,
							t2.Address,
							t2.Telephone,
							t2.Image
				)

quote:

---

Originally posted by abuhassan

i have duplicate records, but they have unique ids.

the duplicates resulted as a result of using a view to enter the data using BCP to load data from text file that had many dulicate records as part of consolidting the data i would like to get rid of the duplicate

is there some way of checking if the Name, Address, Telephone and Image are the same as on a record with another then remove them.

the table has the following colunms:

ID, Name, Address, Telephone, Image

1, adam, 45 tree gate road, 00000 000000, adam.jpg
2, uno, 44 garstang road, 00001 000001, uno.jpg
3, adam, 45 tree gate road, 00000 000000, adam.jpg
4, brian, belgarth house, 00022 000022, adam.jpg
5, karen, 3 chester close, 00002 000002, adam.jpg
6, uno, 44 garstang road, 00001 000001, uno.jpg
7, adam, 45 tree gate road, 00000 000000, adam.jpg


from the sample data you can see that there are multiple records is there a way of clensing the data using a sql command?

is there any issues such as performance on clensing it etc?

are there any strategies that should be implemented at the data tier so as to prevent duplication of data?

---