How to remove dup entries

Hi All
In my application i have to get the data from .csv file. My requirement is that file may consists of duplicate entries
I ant to remove the dup entries and i want to place in the table.
Waiting for valuable replies
Thank u
Baba

Suppose the text file is:

1, MAK, A9411792711, 3400.25
2, Claire, A9411452711, 24000.33
3, Sam, A5611792711, 1200.34
2, Claire, A9411452711, 24000.33

And you want to import it into table T1

Here it is:

SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1;
IF OBJECT_ID('T2') IS NOT NULL
DROP TABLE T2;

CREATE TABLE T1(
id int primary key,
[Name] sysname,
code sysname,
num decimal(10,2)
)
GO

SELECT * INTO T2 FROM T1 WHERE 1 = 2;

BULK INSERT tempdb.dbo.T2 FROM 'd:\tmp\test.txt'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

INSERT INTO T1 SELECT DISTINCT * FROM T2;
GO

SELECT * FROM T1;

The output is:

id Name code num
-- -- --
1 MAK A9411792711 3400.25
2 Claire A9411452711 24000.33
3 Sam A5611792711 1200.34

|||

hi

what is your way to import the data. If it is a ETL process with a select-statement use

select distinct * from yourcsv

The other way is, to import in a temp-table. after this copy the data to the correct table with

insert table

select distinct * from temptable

To get all dup entries use

select * from table where [onecolumnofrow] in

(select [samecolumn] from table group by [samecolumn] having count(*) > 1)

If there is only ONE dup entri you can delete this with DELETE top 1 * with the same where-clause

Bye

Thorsten Ueberschaer

sql