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
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.
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
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 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
In 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)
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.
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.
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'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:
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?
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
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')
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.
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
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 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?
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
How do I update a record that has duplicates. For example, I have 3612 orders some of these orders have multiple orderid's I want to update the record for each of these orders that was added most recently.
Hi All, Here is my story, how to change a column called Flag_Status based on the maximum Updated date. i.e. i want to make Flag_Status be 1 for the records which have maximum Updated_date (current record) and the rest to make it 0. for example accountID 1 has three records updated, but only one is current the rest are historical, thus i want the history record to be Falg_status 0 and the current record be 1. Note that Inserted_Date and Updated_Date are created using SSIS Derived column During loading the source table, it helps me when each record is inserted into the Data warehouse.
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)
