how to remove duplicate records from incoming textfiles

Is there a way to check if duplicates exists in the incoming textfiles?

Run the text file through a sort transformation and select the "remove duplicates" check box.|||using sort exceution seem to be too slow....is there any other way?|||

sureshv wrote:

using sort exceution seem to be too slow....is there any other way?

Yep,

Some related links for you that will help:

A distinct component please

(http://blogs.conchango.com/jamiethomson/archive/2006/12/08/SSIS_3A00_-A-distinct-component-please.aspx)

How to get Distinct Count in SSIS

(http://sqlblog.com/blogs/marco_russo/archive/2007/03/09/how-to-get-distinct-count-in-ssis.aspx)

NSort (High performance sort component that contains functionality to remove duplicates)

(http://www.ordinal.com/NsortSSIS.pdf)

"Distinct" related posts from the SSIS Search Macro

(http://search.live.com/results.aspx?q=distinct&form=QBRE&q1=macro%3Ajamiet.ssis)

-Jamie

|||You could also load that flat file as is to a staging table in SQL server then use a select distinct from that staging table.

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

how to quick query duplicate records?

any idea?

quick query duplicate records (speicifed fields.value are same) using T-SQL?

depends on how many columns you've got in the table but as an example:

say you have a table called Customer with one column called CustomerID then this simple query will tell you.

SELECT CustomerID, COUNT(*)

FROM Customer

GROUP BY CustomerID

HAVING COUNT(*) > 1

ORDER BY 2 DESC

|||

Thanks your reply what i want is

display duplicate records depends on multi-column

regards

|||

just add the extra columns in to the SELECT and GROUP BY clauses and it will work for multiple columns.

even better, can u post an example of the data that you are wanting to look for duplicates in?

|||

At the same time if you table own identity field you could use another query. Keeping on mind identity value you drop the one own less value.

I'm sorry but I don't have that query

|||

Because i want to remove duplicate records,so have to display first i think

I found an article

http://www.sqlservercentral.com/columnists/chawkins/dedupingdatainsqlserver2005.asp

I think it's cool, sharing