InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 03:23:06 GMT20http://www.sqlservercentral.com/Forums/Topic529777-1195-1.aspxThanks, I will apply that logic, seems much simpler.Thu, 10 Jul 2008 00:02:42 GMTNeel Singhhttp://www.sqlservercentral.com/Forums/Topic529777-1195-1.aspxFirst, 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 columnthat could be used to identity the most recent record for a commoncolumn or set of columns.For example, your given table has repeating address informationfor a pediatrician (ped_?). since the pediatrician's name couldbe the same for each of this pediatrician's records then you wouldidentify the most recent record for the entire set of that's pediatrician'srecords using a query like this: Assume we have an IDENTITY column named: record_idSELECT * FROM dbo.SomeTable A, (SELECT ped_Name, MAX(record_id) as MAX_ID FROM dbo.SomeTable GROUP BY ped_name) as BWERE a.record_id=b.MAX_IDWed, 09 Jul 2008 11:36:07 GMTDallas Martinhttp://www.sqlservercentral.com/Forums/Topic529777-1195-1.aspxHi 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 tableselect pho_phone_number,count(*) ccinto #tempfrom XD_HM_Extract with (nolock)where data_type = 'XD'group by pho_phone_numberhaving count(*) > 1--select accounts with no duplicate into a temp tableselect 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,statusinto #mainfrom 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 accountsselect 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,statusinto #dupfrom 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 duplicateselect *into #main1from #dup xdwhere 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 tablesinsert into #mainselect *from #main1/*drop table #maindrop table #main1drop table #tempdrop table #dup*/select *from #mainWed, 09 Jul 2008 02:08:05 GMTNeel Singhhttp://www.sqlservercentral.com/Forums/Topic529777-1195-1.aspxHi 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.Wed, 09 Jul 2008 01:57:10 GMTamar_mazharhttp://www.sqlservercentral.com/Forums/Topic529777-1195-1.aspxHi 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 rows4. 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? PeterLWed, 09 Jul 2008 00:09:54 GMTPeter Lavellehttp://www.sqlservercentral.com/Forums/Topic529777-1195-1.aspxHi 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 Tue, 08 Jul 2008 00:10:46 GMTNeel Singh