I have been given a task to locate duplicate and report duplicate records and am trying to determine the best way to do this with databases that have 1 million records plus.
Say I have a table with 20 columns, I need to check to see if 3 of 10 specific columns match.
So if 2 columns are the same its no problem however if 3 or more match, they are considered duplicate.
I have student table where duplicate student exist by name with there fathers name and mothers name. I need to search those duplicate records. I do not need ti count them but If there is 5 same student with name then the query will show 5 name then I will delete individually. Below I am trying to show the scenario.
Student_name  Â
_____________
Rocky
Albert
Rocky
Williams
Albert
Robert
The query will show
Student_name  Â
______________
Rocky
Rocky
Albert
Albert
I have a table that "Geography" that has the following columns: city, state, zip
There are tons of duplicate cities in this table. I ran this query and it shows me the number of occurrences of each city. I want to delete all the duplicates except for 1. I don't want to do this manually as there are a lot of records.
What would the SQL look like to delete the duplicate records but keep at least one?
My query wants to insert new supplier if there is any. And it should ignore, if the supplier is already present in the table. But it is trying to insert the supplier which is already available. For example, I have PART A with 2 suppliers ABC and DEF. I am getting data from third party for PART A with supplier DEF. As per the condition, it should ignore the record because DEF is already available . But my query is trying to insert supplier DEF and following that, I am getting primary constraint error.
-- Inserting new preferred supplier into R5CATALOGUE
DECLARE @DATEPROCESS DATETIME;
SET @DATEPROCESS = CAST(DATEADD(D, -((DATEPART(WEEKDAY, GETDATE()) + 1 + @@DATEFIRST) % 7), GETDATE()) AS DATE)
INSERT INTO R5CATALOGUE(CAT_PART, CAT_SUPPLIER,CAT_GROSS,CAT_LEADTIME,CAT_PURUOM,CAT_REF,CAT_MULTIPLY,CAT_CURR,CAT_SUPPLIER_ORG,
CAT_PART_ORG,CAT_DESC,CAT_MINORDQTY)
[code]....
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies View RelatedJust like Unique/Distinct command, is these some way I could list just the duplicate records from a table . The field is numeric.
Thanks a lot for you help.
Hi I have created a simple application base on the video based on the 'SQL Server 2005 Express for Beginners' video No. 8.It is working great but I would like to add another feature; if an emial address (in my case userName) is entered that already exists in the database I would like to display an message say you have already subscrided.How do I search the database for a duplicate email address? Please keep it simple I am still finding my feet with aspx and c#.Cheers protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
SqlDataSource profilesDataSource = new SqlDataSource();
profilesDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["profilesConnectionString1"].ToString();
profilesDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
profilesDataSource.InsertCommand = "INSERT INTO staffprofiles (userName, fullName, nickName, favouriteSaying, memorableMoment, song, farewell) VALUES (@userName, @fullName, @nickName, @favouriteSaying, @memorableMoment, @song, @farewell)";
profilesDataSource.InsertParameters.Add("userName", userNameTextBox.Text);
profilesDataSource.InsertParameters.Add("fullName", fullNameTextBox.Text);
profilesDataSource.InsertParameters.Add("nickName", nickNameTextBox.Text);
profilesDataSource.InsertParameters.Add("favouriteSaying", favSayingTextBox.Text);
profilesDataSource.InsertParameters.Add("memorableMoment", memMomentTextBox.Text);
profilesDataSource.InsertParameters.Add("song", songTextBox.Text);
profilesDataSource.InsertParameters.Add("farewell", farewellTextBox.Text);
int rowsAffected = 0;
try
{
rowsAffected = profilesDataSource.Insert();
}
catch (Exception ex)
{
// Report a problem
Server.Transfer("submit_problem.htm");
}
finally
{
profilesDataSource = null;
}
if (rowsAffected != 1)
{
// Report a problem
Server.Transfer("submit_problem.htm");
}
else
{
// Everything was OK
Server.Transfer("submit_success.aspx");
}
}
Dear All,
I need to identify duplicate records in a table. TableA [ id, firstname, surname] I’d like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table
I’m not sure how to write this query, can someone help? Thanks in advance!
Hi guys how do you hide duplicate records, how would I do a select statement for that
In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 )
I have about had it with this database I have been asked to make a report out of
This is part of my trigger on table T1. I am trying to check if the records inserted to T1 is available in myDB.dbo.myTable or not (destination table). If it is available rollback T1. It does not do that although I insert the same records twice.
-- duplicate record check
SET @step = 'Duplicate record'
IF EXISTS (
SELECT i.myID, i.Type
FROM INSERTED i INNER JOIN
myDB.dbo.myTable c ON i.myID = c.myID
GROUP BY i.myID, i.Type
HAVING (COUNT(*) > 1) AND (i.Type = 'In')
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @step)
Return
END
What is problem?
Hi EverybodyThis Code duplicate the record in the database, can somebody help me understand why that happen. Thanks a LOT CompanyName: <asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>" insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@CompanyName, @Phone)" selectcommand="SELECT * FROM [items]"> <insertparameters> <asp:controlparameter controlid="txtCompanyName" name="CompanyName" /> <asp:controlparameter controlid="txtPhone" name="Phone" /> </insertparameters></asp:sqldatasource> VBPartial Class Default2 Inherits System.Web.UI.Page Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End SubEnd Class ----------------------------------------------Yes is an Identity the Primary Key of the Table items
View 2 Replies View RelatedIn order to check that a new users ID does not already exist in the database I thought it would be a good idea to put the Insert into a Try Catch statement so that I can test for the duplicate record exception and inform the user accordingly. I was also trying to avoid querying the data base before executing the Insert.
The problem is what to actually test for. When the code throws the exception it is a big long string . .
"Violation of PRIMARY KEY constraint 'PK_Users_2__51'. Cannot insert duplicate key in object 'Users'"
I just thought that there has to be something simplar to test for than comparing the exception to the above string.
Can anyone tell me of a better way of doing this ?
(by the way I am only using Web Matrix and MSDE in case it matters)
Mark
I am working on a web application that utilizes a sql server database. One of the tables is a large text file that is imported through a DTS package from a Unix server. For whatever reason, the Unix box dumps quite a few duplicate records in the nightly run and these are in turn pulled into the table. I need to get rid of these duplicates, but can't seem to get a workable solution. the query that is needed to get the records is:SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName, tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone, tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2, tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTimeFROM tblAppointments CROSS JOIN tblPTDEMO2WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)AND tblAppointments.Insurance1 = 'MED'AND tblAppointments.ApptTypeID <> 'MTG'AND tblAppointments.ApptTypeID <> 'PNV'AND DateDiff("dd", ApptDate, GetDate()) = 0Order By tblAppointments.ApptDateMy first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query. My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails. I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own. I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing. Any help on this topic would be greatly appreciated. Thanks.
View 5 Replies View Relatedi'm a newbie to sql , anyone can give me suggestions on how to
remove duplicate records in a table, a table also has primary key,
thanks
So I'm working on updating and normalizing an old database, and I have some duplicate records that I can't seem to get rid of. Every column is identical, right down to what is supposed to be the key. I can't right a delete query to just isolate one row, and I can't delete (or even udpate) any row in management studio. Any thoughts on how to remove the extra rows?
There is a field that's supposed to be unique, so I can write a simple query to get all of the problem rows. The only thing is that they come back in pairs.
hi to all,
How to delete duplicate record in the recordset?
Thanks...
Hi everybody,
I have 2 fields in a table.
Table Name--- StudentDetail
Name Address
Saju Kerala
Balaji Bangalore
Raj Kumar Tamilnadu
Saju Kerala
I want to Update one of the duplicate row as I don't have any unique id column. So can anybody update one of the the duplicate record without using any id or altering any column.
I am waiting for your reply.................
Regards,
Saju S.V
Hi everybody i need help on on a query on how i can extract this... with the following table below..
id pub
1 a
1 b
2 c
2 c
I need to extract only the id and pub where pub has more than one with the same id... in the case of the above the result would be
id pub
2 c
2 c
thanks
How can i keep only one record from duplicate entry.
Example
COLUMN1COLUMN2COLUMN3COLUMN4
AAA121-12
AAA121-13
AAA121-14
Here i want to keep only the top record and want to delete other 2 rows.
Hi ,
How can i delete the duplicate record from a table
use Northwind
create table Emp (Ecode char(2), Ename char(10))
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A4','D')
Insert into Emp(Ecode, Ename) values('A4','D')
select * from emp order by Ecode
Thanks
ASM
Hey Again,
I've been making great progress but I've hit another road block which a newbie intern like myself can't surpass. What's worse is the fact that no one is in the office today! Maybe someone can point me in the right direction with this SQL:
SELECT
r.[requestID]
,r.[requserID]
,r.[departmentID]
,CONVERT(CHAR(8),r.[submitDate],10)AS submitDate
,CONVERT(CHAR(8),r.[dueDate],10)AS dueDate
,CONVERT(CHAR(8),r.[revisedDueDate],10) AS revisedDueDate
,r.[reqStatus]
,r.[completedDate]
,d.[departmentName]
,s.[statusName]
,u.lastName + ', ' + u.firstName AS submittedBy
,ra.userID
FROMtblUserDepartment ud
INNER JOIN tblRequest rON ud.departmentID = r.departmentID
INNER JOIN tblDepartment dON r.departmentID = d.departmentID
INNER JOIN tblStatus sON r.reqStatus = s.statusID
INNER JOIN tblUser uON r.requserID = u.userID
LEFT JOIN tblRequestAssignee ra ON r.requestID = ra.requestID
WHEREud.userID= @userID
This works great except for one thing. In tblRequestAssignee, you have 1 primary assignee and can have several other assignees (that are not primary). This is denoted by a bit field "isPrimaryAssignee" in tblRequestAssignee. When I run the query, I see every request I want to but it duplicates requests with more than one assignee. What I'm trying to do is make only the primaryAssignee display if there is one. If there's not, then null is displayed (which is already happening).
Like I said, the query is mostly working right except for this duplicate record that displays when there's 2 assignees. Any help would once again be greatly appreciated.
1 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
2 Dim sqlStr As String
3 Dim sqlStr2 As String
4 Dim myConnection As MySqlConnection = Nothing
5 Dim myCommand As MySqlCommand = Nothing
6 Dim myConnection2 As MySqlConnection = Nothing
7 Dim myCommand2 As MySqlCommand = Nothing
8 Dim myReader As MySqlDataReader = Nothing
9 Dim IC As String
10
11 IC = txtIC1.Text + "-" + txtIC2.Text + "-" + txtIC3.Text
12
13
14 Try
15 sqlStr2 = "SELECT * FROM User WHERE uLogin='" & txtUserName.Text.Trim() & "'"
16
17 ' Connection
18 myConnection2 = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString())
19 myConnection2.Open()
20 ' Command
21 myCommand2 = New MySqlCommand(sqlStr2, myConnection2)
22 ' Reader
23 myReader = myCommand2.ExecuteReader()
24 Catch ex As Exception
25 ' Exception Error Here
26 Response.Write(Err.Number & " - " & Err.Description)
27
28 End Try
29 ' Checking
30 If myReader.Read() Then
31
32
33 Label2.Text = "Username already exist. Please choose another username"
34 Label3.Text = "*"
35
36 Else
37
38 Try
39
40
41 sqlStr = "INSERT INTO userapplication(uaName,uaIC,) VALUE (?uName,?uIC )"
42
43
44
45 ' Connection
46 myConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString())
47 myConnection.Open()
48 'Command
49 myCommand = New MySqlCommand(sqlStr, myConnection)
50
51 myCommand.Parameters.AddWithValue("?uName", txtName.Text)
52 myCommand.Parameters.AddWithValue("?uIC", IC)
53
54
55 myCommand.ExecuteNonQuery()
56 myConnection.Close()
57 Response.Redirect("Register.aspx", False)
58
59 Catch ex As Exception
60 ' Exception Error Here
61 Response.Write(Err.Number & " - " & Err.Description)
62 Finally
63 ' Clean Up
64 If Not IsNothing(myCommand) Then
65 myCommand.Dispose()
66 End If
67 '
68 If Not IsNothing(myConnection) Then
69 If myConnection.State = Data.ConnectionState.Open Then myConnection.Close()
70 myConnection.Dispose()
71 End If
72 End Try
73
74
75 End If
76
77 End Sub
78
79
above is my code for the user registration page.the code that i bold,which with number 55,56 and 57,are where the problem occur.
when it run,it run 55, then 57,then back to 55, then 57 again
means that my db hav duplicate record being insert
anyone know how to solve this problem?
I have a table with 2 columns, col1 is unique, col2 is not.
col1 is numeric col2 is varchar.
Here is the problem,
col2 will have duplicate values, I need the largest numeric value from col1 with unique value from col2.
Thanx for any help.
Using Transact-SQL how can I copy all fields except one from one record to another? The field in question being the identity field. Since, this field cannot be duplicated a simple INSERT statement fails. How can I specify an exclusion list of fields?
-Sumit
I have the following query I am using to identify duplicate records in one of my database tables:
Code:
SELECT memberID,
COUNT(memberID) AS NumOccurrences
FROM ChapterMembers
GROUP BY memberID
HAVING ( COUNT(memberID) > 1 )
Executing the above proc returns 4079 records...
Now, I would also like to know the ChapterID for each member with a duplicate record. ChapterID is also stored in the ChapterMembers Table...
I tried running the following procedure:
Code:
SELECT memberID,
COUNT(memberID) AS NumOccurrences, chapterID
FROM ChapterMembers
GROUP BY memberID, chapterID
HAVING ( COUNT(memberID) > 1 )
But zero results are returned ...
The ultimate goal here is to identify duplicate records where one of their chapterID's = '81017' and to delete that record from the database.
Anyone have any ideas what I am doing wrong? Also, any suggestions for removing the records would be appreciated.
Thanks,
Jandrews
I am new in SQL programming world, following is query that i had created
select interfaces.nodeid as 'Node Id',
nodes.caption as 'Node Name',
netflowsources.Lasttime as NetflowLastTime
from interfaces inner join nodes on interfaces.nodeid = nodes.nodeid
inner join netflowsources on interfaces.interfaceid = netflowsources.interfaceid
where netflowsources.LastTime NOT LIKE GETDATE()
which is from that query i get a return successfully, but i just noticed that, for column nodeid was showed me multiple duplicated records, for example
nodeid value = a,a,a,b,b,c,c,c,c,d,d,d
But what i expected was to get a return without any duplicate record within it. and i also have tried using "distinct" command, but that only impacted on "a" value, but others value not change at all.
Hi ,
i am using sql server 2005.
i have one table where i need to find records that have same citycode and hospitalcode and doctorcode then delete the record keeping only one record of them
my problem is table structure have idendtity column which is unique.
that is m table structure is something like
recid citycode hospcode doctorcode otherdesp
1 0001 hp001 d0001 ...
2 0002 hp002 d0002 ...
3 0001 hp001 d0001 ...
4 0002 hp002 d0002 ...
please suggest
thank you
Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!
Thanks in advance, Larry
ALTER FUNCTION dbo.TestFoodDisLikes
(
@ResidentID int
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @RDLike varchar(50)
DECLARE @RDLikeList varchar(250)
BEGIN
SELECT @RDLikeList = ''
DECLARE RDLike_cursor CURSOR
LOCAL SCROLL STATIC
FOR
SELECT FoodItem
FROM tblFoodDislikes
WHERE (ResidentID = @ResidentID) AND (Breakfast = 'True')
OPEN RDLike_cursor
FETCH NEXT FROM RDLike_cursor
INTO @RDLike
SELECT @RDLikeList = @RDLike
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM RDLike_cursor
INTO @RDLike
SELECT @RDLikeList = @RDLikeList + ', ' + @RDLike
END
CLOSE RDLike_cursor
DEALLOCATE RDLike_cursor
END
RETURN @RDLikeList
END
ex. from source the file you want to split the record into two, the one with a clean record and the other one with duplicate records
Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this article http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005
I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000. Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO Sub AddUserLog(ByVal Username As String)
Dim SqlText As String
Dim cmd As SqlCommand
Dim strIPAddress As String
'Get the users IP address
strIPAddress = Request.UserHostAddress
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
SqlText = "sp_AddUserLog"
cmd = New SqlCommand(SqlText)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con
cmd.Parameters.Add("@Username", SqlDbType.VarChar, 100).Value = Username
cmd.Parameters.Add("@IP", SqlDbType.VarChar, 100).Value = strIPAddress
Try
con.Open()
cmd.ExecuteNonQuery()
Finally
con.Close()
End Try
End Sub
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable. I need to check whether count > 0 or not in stored procedure.I have used below query.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
I am new to SQL and these forums, so please bear with me :)
My basic question is if I can create a update trigger that will pull info from another record in the same table if data in certain fields match the existing record.
An example:
The user creates a new record. If said user enters data in specified fields that matches data in the same fields in another record in the same table, can a update trigger be used to fill out the rest of this record with the data from the record that matches?
If you need more Info on my problem, ask and I will try to explain better. There may be a better way of doing this than using a trigger, but I am not sure. The fields that I would use to match the data would not be the primary key fields.
Thanks!