Select into a Text file from a Table and removing dupilcates

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jeff Luckett » Select into a Text file from a Table and...

Select into a Text file from a Table and removing dupilcates

Rate Topic

Display Mode

Topic Options

Author

Message

Neel Singh

Posted Tuesday, July 08, 2008 12:10 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 1:19 AM
Points: 15, Visits: 40

Hi

I have a table of about 21 000 000 rows, I need to export this data to a text file and before exporting I need to remove all duplicates. What would be the fastest and most efficient way to do this task.

Thanks

Post #529777

Peter Lavelle

Posted Wednesday, July 09, 2008 12:09 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 8:03 PM
Points: 54, Visits: 1,497

Hi Neel,

Could you provide some more details please?

1. What version SQL Server?
2. Table definition (table name, column names, data types, keys, indexes)?
3. Some sample data rows
4. What exactly do you mean by duplicate rows? If three rows are duplicates do you want to delete all of them, the first two or the last two? There are many postings on this site about eliminating duplicate rows.
5. Is this a once-off request or do you want to run this regularly?

PeterL

Post #530500

amar_mazhar

Posted Wednesday, July 09, 2008 1:57 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 6:04 AM
Points: 43, Visits: 51

Hi Neel,

I think you can use DTS package to transfer data from table to text file. In Dts packages you can use store procedures or TSQL quesries to control duplicate values.

Post #530552

Neel Singh

Posted Wednesday, July 09, 2008 2:08 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 1:19 AM
Points: 15, Visits: 40

Hi

For inserting the data into the text file, I did a striaght select into the text file using sql query.

For removing Duplicates I used the below query but I'm sure there is a better option.

--select all dupicates into a temp table
select pho_phone_number,count(*) cc
into #temp
from XD_HM_Extract with (nolock)
where data_type = 'XD'
group by pho_phone_number
having count(*) > 1

--select accounts with no duplicate into a temp table
select acc_account_code,
ped_initials,
ped_name_1,
ped_surname,
adr_line_1,
adr_line_2,
adr_line_3,
adr_line_4,
adr_post_code,
que_code,
que_description,
bck_id,
pho_phone_number,
data_type,
status
into #main
from XD_HM_Extract xd with (nolock)
where not exists (select *
from #temp t
where xd.pho_phone_number = t.pho_phone_number)
and data_type = 'XD'

--select all duplicate accounts
select acc_account_code,
ped_initials,
ped_name_1,
ped_surname,
adr_line_1,
adr_line_2,
adr_line_3,
adr_line_4,
adr_post_code,
que_code,
que_description,
bck_id,
xd.pho_phone_number,
data_type,
status
into #dup
from XD_HM_Extract xd with (nolock)
join #temp t with (nolock)
on (xd.pho_phone_number = t.pho_phone_number)
where data_type = 'XD'

--select distinct accounts where phone number is duplicate
select *
into #main1
from #dup xd
where xd.acc_account_code = (select top 1 t.acc_account_code
from #dup t
where xd.pho_phone_number = t.pho_phone_number
order by acc_account_code desc)

--merging my two temp tables
insert into #main
select *
from #main1

/*
drop table #main
drop table #main1
drop table #temp
drop table #dup*/

select *
from #main

Post #530556

Dallas Martin

Posted Wednesday, July 09, 2008 11:36 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, January 28, 2011 12:21 PM
Points: 14, Visits: 33

First, you need to understand your data.

Exactly what makes anyone record the "unique" or desired record?

Does the table have an IDENTITY column or a datetime column
that could be used to identity the most recent record for a common
column or set of columns.

For example, your given table has repeating address information
for a pediatrician (ped_?). since the pediatrician's name could
be the same for each of this pediatrician's records then you would
identify the most recent record for the entire set of that's pediatrician's
records using a query like this:

Assume we have an IDENTITY column named: record_id

SELECT * FROM dbo.SomeTable A,
(SELECT ped_Name, MAX(record_id) as MAX_ID
FROM dbo.SomeTable
GROUP BY ped_name) as B
WERE a.record_id=b.MAX_ID

Post #531116

Neel Singh

Posted Thursday, July 10, 2008 12:02 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 1:19 AM
Points: 15, Visits: 40

Thanks, I will apply that logic, seems much simpler.

Post #531376

« Prev Topic | Next Topic »

Permissions