Home » SQL ServerRSS

Duplicates in sql db

Hi, i'm very new to sql server but my boss have assigned me a new task of taking care of all the database. we r not very big company but we have quite a bit of data in our data base. i have 3 different tasks i would like to start with 1 of them today, now i have done this 10years ago but now is just fade memories left. so my first task is to build a very small database which can find duplicate rows… 

 

whit i mean is:

i have database name import&export  and table called Transportation. 

 

then i have columns like:

sending Id (which will be PK and can't be not null)

project no. (not null again)

Bill no.(not null)

Paid(like bill paid or not/ not null)

and so on…

 

now my boss get this file from the transport company in Microsoft EXEL file and there he can not see if the Transport company have charged him TWICE or maybe more (and trust when i say they do do that a lot) 

now i just want to build a database in Sql server 2008 r2 which can take care all of this TASKS for him. 

task1: he import MS eel file into the database.

task2: the database will show if there is any Duplicate ROWs

 

I really want to learn please help..

Many Thanks and Marry Christmas to all... :)

 

Regards 

Mann

 

7 Answers Found

 

Answer 1

 

Hi

Is there Any Uniqe column (ie. Project no or some thing)  in that case you can insert excel sheet into temptable first then

write customized script to remove the duplicate column compare with Original table (Import&export table).

 

Answer 2

Check below
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/879ee1e5-e443-4ebc-9fb0-062644db2a7c
Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq
 
 
 

Answer 5

Hey back, first of all thanks to all of you for taking your time answer... 

first Nag pal the Sendning ID is unique column. but i don't want to remove the duplicate i want to see so i can show it to the Transport company.

Mr. Balmukund thx but thats not what i'm looking for i want to see those Duplicates...

Uri Dimant thx for your time. just one thing i went to the task one LINK but there it was not mention about SQL server 2008 but i will try as same as sql server 2005 let see if it worked... 2nd link : i don't to delete those Duplicates.

 

i would like the database show me that which rows have duplicate, If possible the duplicate should come under the original row so eassy to mark... 

But thx a  lot every one.....

lets see if i get some more answers... 

Mann

 

Answer 6

Hi JBSync,

 

We could use the  OPENROWSET function or the Import and Export Wizard to import data from the Excel file into SQL Server 2008 R2, please take a look on the following example:

 

Use the distributed query import the data from Excel to SQL Server:
 

--import a 2007-2010 Excel file 

SELECT* INTO TESTFROMOPENROWSET('Microsoft.ACE.OLEDB.12.0',

   'Excel 12.0;HDR=YES;Database=E:\Test\Test.xlsx',

   'SELECT * FROM [Test$]')

GO 

    

--import a 97-2003 Excel file 

SELECT* INTO TESTFROMOPENROWSET('Microsoft.ACE.OLEDB.12.0',

   'Excel 8.0;HDR=YES;IMEX=1;Database=E:\Test\Test.xls',

   'SELECT * FROM [Test$]')

GO

However, before we execute the above statements, we need to enable the 'Ad Hoc Distributed Queries' options:

 

EXECsp_configure'show advanced options',1

RECONFIGURE

EXECsp_configure'Ad Hoc Distributed Queries',1

RECONFIGURE

 

For your second questions, to find the duplicate rows in the table, please refer the below statements:
 

WITH CTE

AS

(

SELECT sending_id, project_no, Bill_no, paid, ROW_NUMBER() OVER( PARTITION BY project_no, Bill_no, paid, [other rest columns] ORDER BY sending_id DESC)AS ROW_NO FROM TEST

)

SELECT sending_id, project_no, Bill_no, paid FROM CTE WHERE ROW_NO>1

 

In addition, about how to use Import and Export wizard, please reference the following links:

Importing and Exporting Data by Using the SQL Server Import and Export Wizard

http://dotnetslackers.com/articles/sql/Importing-MS-Excel-data-to-SQL-Server-2008.aspx (This is an third-party link just for your reference, hope this article helpful since it provide the steps with images )

 

If there is anything unclear, please feel free to ask.

 

Thanks,

Weilin Qiao

 

Answer 7

Hi WeiLin Qiao, firstly A very happy New Year..

 2nd i really appreciate for your time. i have red your answer but i still have some questions if you don't mind answering them..

Q1. Is there any system to import the data into the same Table that i have Created with First Excel File or i have to  Create a New Table every time?  

Q2. the Query you have sent me for finding Duplicate Rows will this work Automatically or do i have to run this every time when i Import a new file?

 i have one Extra Question to ask if it is ok with you!! coz i have been looking for the right answer every where but didn't quite got it yet...

 This is how my table looks like:

Column Name:                Data                  Data Type? (i want to which is the right datatype to use there is to many choices)

Fakturerat kundnr            64107873        Float

Faktura-datum             20101008           Float

Faktura-nummer      563264308         Float

Kredit-faktura             N                       nvarchar (255)

Produkt                     Gods Inrikes       nvarchar (255)

Sändning                      6285202401        Float

Avs-datum                      20100719            Float

Avs-land                      46                       Float

Avs-postnr                      64130                 Float

Mott-land                      46                      Float

Mott-postnr             74940                 Float

Avsändare                      EUMENT AB         nvarchar(255)

Referenstext             73672538            Float

ReferensStad                  AMIRRORA           nvarchar(255)

Fraktgrundande vikt, kg    2500                   Float

Verklig vikt, kg               600                      Float

Antal kolli                      2                         Float

Deb.grundande enhet     PPL                      nvarchar(255)

Deb.grundande antal     2                          Float

Kund-pris, sek             434                       Float

Sändnings-pris, sek     450                       Float

 

Please tell me if i am using the Right data type for this table?

Thank you

Mann 

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter