Writting Trigger Or Procedure To Delete Duplicate Entries In A Table?
I am using Sql Server 2000.
I have a customer table with fields - CustId, Name, Address, City, StdCode, Phone.
I used to insert entries in this table from an excel file.
One excel file will contain thousands of customer.
In this table combination of StdCode and Phone should not be repeated.
If I do it in my VB.Net coding.then application gets drastically slow.
So I want to write a procedure or trigger for this.
Here what I will do, I will send all records into database then this trigger or procedure will check for
any existing entry of combination of StdCode and phone. If entry exists then this will delete new entry
or will not allow this new entry.
Is this possible to do using Trigger or stored procedure?
View Complete Forum Thread with Replies
Related Forum Messages:
Duplicate Entries In The Resulting Table
Hi! I am joining 3 tables in SQL , I am getting the results I want exept it's duplicated. So the resultinmg table fom my stored procedure has 3 rows that have the same bulletin. How do I filter the storedprocedure to output only the rows that don't have duplicate entries for the column 'Bulletin' Thanks. Here is my stored procedure:PROCEDURE [dbo].[spGetCompBulletins] @Userid uniqueidentifier OUTPUT,@DisplayName varchar(200) AS SELECT * FROM dbo.UserProfile INNER JOIN dbo.bulletins ON dbo.UserProfile.UserId = dbo.bulletins.Userid INNER JOINdbo.Associations ON dbo.Associations.BusinessID = dbo.bulletins.Userid WHERE UserProfile.DisplayName=@DisplayName and Userprofile.Userid = @Userid ORDER BY Bulletins.Bulletin_Date Return
View Replies !
Prevent Duplicate Entries In A Table
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember. The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID. Here is a sample of some data: User1 Group1 User1 Group2 User2 Group2 User3 Group1 User3 Group3 Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example: User1 Group1 User2 Group2 User1 Group1 I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome. Is there a simpler way to prevent duplicate entries in a table using sql? Thanks a lot, Chris
View Replies !
Truncating Duplicate Entries In A Table
Hi, I have a table with no primary key and i just want to see all the duplicate entries on the basis of two columns. Can anyone suggest me how should i go about it. Can anyone provide me the syntax for the same? I have only 1 table say ISSR_TBL and two columns using which i want to delete the duplicate ones. i.e. MIN and MAX. Please help me out...
View Replies !
Writting A Trigger
Hello, I have to write a trigger to truncate a table at a scheduled time.Truncate statement is not allowed in the trigger.What should I do.I mean how to go for it.How can I test my trigger if it's working fine or not. Thanks for any help. JAY
View Replies !
Question About Writting A Trigger
hello. i have two table so that they are following form: tblCardex(Fd_GoodsId,Fd_RepositoryId,Fd_DateTime,Fd_Type,Fd_UnitCoefficent) tblGoodsStock(Fd_GoodsId,Fd_RepositoryId,Fd_UnitCoefficent) Fd_GoodsId,Fd_RepositoryId -->inteager Fd_UnitCoefficent --> decimal Fd_Type --> bit (0 means input and 1 means output) i wish writting a trigger on Insert of tblCardex.in this trigger ,i must do following operations: if(tblGoodsStock.Fd_GoodsId=Inserted.Fd_GoodsId and tblGoodsStock.Fd_RepositoryId=Inserted.Fd_RepositoryId) begin if(Inserted.Fd_Type==0) tblGoodsStock.Fd_UnitCoefficent = tblGoodsStock.Fd_UnitCoefficent+ Inserted.Fd_UnitCoefficent else --Inserted.Fd_Type==1 tblGoodsStock.Fd_UnitCoefficent = tblGoodsStock.Fd_UnitCoefficent- Inserted.Fd_UnitCoefficent end tank you.
View Replies !
How To Delete Duplicate Records From Table ?
Hello Frnds....Can anybody give the answer of this question as How to Delete duplicate records from Table ? I Know that with check option and also with Unique Constraint we can avoid to enter duplicate records in table but How to delete from table which does not have any constraints ?
View Replies !
Duplicate Entries In SQL W/ IE 5.5
I am getting duplicate entries in SQL database(7.0 or 2000)when users running IE 5.5 or higher access my ASP pages. We are running IIS 4.0, but the problem occurs with IIS 5.0 also. I'm finding no information on this problem...has anyone else had the same experience? Thanks
View Replies !
Duplicate Entries
I have an issue where certain parts of data are repeated several times after i create my query. Without providing my SQL code for now could anyone suggest possibly the main reason(s) for data being duplicated? Thanks
View Replies !
Duplicate Entries
I have an application that allows the user to enter data into a table. There are multiple users so I put in some code that, I thought, would keep 2 users from creating a new record at the same time. The IDs for the records are identical and this is causing a problem. The IDs are in the format of ####-mmyy. at the start of each month the #### part goes back to 1. We tried a test today where we had 2 users click on the New button at exactly the same time. The IDs that were created were identical. Is there anyway on the database that I can prevent this from happening? Here is how I create the new record id: I get the MAX(ID) from the table I add 1 to the ID and then insert a new record with the new ID into the table. Any help is appreciated. Thanks, enak
View Replies !
Duplicate Entries
I'm extracting data from a log (log_history) of patients where nurses perform various actions on a call, such as assessing and reassessing, despatching etc. This is the script: Select L.URN, LH.THE_TIMESTAMP, LH.ACTION_TYPE, LH.ACTION_BY, LH.ACTION_REQD, LH.NOTE, em.position_type_ref From LOG L Join Log_history LH on (L.URN = LH.LOG_URN) left outer join employee em on (em.code = LH.action_by) Where (L.Taken_at >= :DateFrom and L.Taken_at <= :DateTo) and (LH.ACTION_TYPE = 'D') and (em.position_type_ref ='NU') Order By L.URN ASC, LH.THE_TIMESTAMP DESC The result I get shows duplicate 'timestamp' entries and I only want to return unique timestamp entries. Does anyone have any ideas. I'm self taught and have hit a wall
View Replies !
Remove Duplicate Entries
I am a newb at ms sql and was hoping someone could help me eliminate duplicate PRODUCT.PRODUCT from this statement. I have tried using DISTINCT with the same results.The ProductImage table is causing this because the duplicates are from the PRODUCT.PRODUCT that have more than 1 image. If anyone could rewrite this statement so I can learn from this, it would be most appreciated! Thank you for your time <asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"SelectCommand="SELECT Product.Product.productid,Product.Product.catid,Product.Product.name,Product.Product.smalltext,Product.Product.longtext,Product.Product.price,Product.ProductSpecial.saleprice, Product.ProductSpecial.feature,Product.ProductImage.imgId, Product.ProductImage.imgUrlFROM Product.ProductINNER JOIN Product.ProductSpecialON Product.ProductSpecial.productid = Product.Product.productidINNER JOIN Product.ProductImageON Product.Product.imgid = Product.ProductImage.imgId"></asp:SqlDataSource>
View Replies !
Duplicate Entries In A Field
I have a field called RegId. RegId is of datatype NVARCHAR (20). RegId ----- 12322 2122111 23423 etc etc I want to run a query to find out if there are duplicate entries in this field. Any ideas on how I can achieve this? Thanks in advance, Anthony
View Replies !
Dont Sum Duplicate Entries
Hi. I have a table with Login and Logoff Time of users, but there could be duplicate Logtimes in the dataset, but for different products. Because of this I cant do a distinct in the dataset. I need the Product and some other details in my Report. I tried to make two datasets. One for the Select distinct and one for the other. But the Problem is: in my report, I need a table, where I make the Sum of the Logintime a day and in another column I calculate with data from the other dataset.(Logtime + data from dataset2). But this doesnt work, so I think, that is it not possible to join 2 dataset in one table. datetime Login | datetime Logout | Product 11.12.2007 10:15 | 11.12.2007 12:15 | p1 11.12.2007 10:15 | 11.12.2007 12:15 | p2 11.12.2007 12:19 | 11.12.2007 15:15 | p2 Is there another option I can do this?
View Replies !
Preventing Duplicate Database Entries
Hi all.. I've been scouring the forums for about 6 hours to no avail. This is a really simple question. I'm trying to have a registration page that lets the user input name, email, desired username, and password. I want to check the username and email fields to make sure ppl cannot sign up twice. So from what I've gathered I have a couple of options: 1) i can set up a unique constraint on the database columns, 2) i can run a select statement before inserting, 3) i can store the whole database column in a variable then search through it. My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.
View Replies !
How Can I Remove Duplicate Entries In A Sql Query?
I have a database being populated by hits to a program on a server.The problem is each client connection may require a few hits in a 1-2second time frame. This is resulting in multiple database entries -all exactly the same, except the event_id field, which isauto-numbered.I need a way to query the record w/out duplicates. That is, anyrecords exactly the same except event_id should only return one record.Is this possible??Thank you,Barry
View Replies !
Duplicate Backup Entries Using SQLMAINT
I would appreciate someone pointing me in the right direction to resolve a backup anomaly were currently experiencing. We recently installed SQL Server 6.5 and noticed that although our scheduled tasks were running as requested the DelBkUps parameter wasn`t working. In addition we noticed that the backup files that were created didn`t have the time portion appended to date (suffix of backup name). The steps we took to resolve this was to install Service Pack 3. After the install the following was observed: 1) DelBkUps parameter started to work 2) duplicate backup entries were created, the only difference being that for one of the entries the time portion was still missing (ie. apps_db_dump.19980722 instead of apps_db_dump.199807221840). Letting in cycle through for a week didn`t have any affect. The final observation is that for scheduled tasks that occur more than once/day (i.e. transaction dump every 8 hours) no duplicate backup entries are created and file suffix is correct (i.e apps_db_dump.199807221840). .......thanks,,,,brad .............
View Replies !
Removing Duplicate Entries In SQL Field
Hi All, Below is a snippet of MS SQL inside some VB that retieves Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense. Below is the SQL and the results from the returned page. PS I tried to use distinct but with no luck I want just one of each in the example below. Thanks in Advance! Scott ============================== SQL = "" SQL = "SELECT B.CIMS_MSDS_NUM," & _ "A.COMMODITY_NUMBER, " & _ "B.CIMS_TRADE_NME," & _ "B.CIMS_MFR_NME," & _ "B.CIMS_MSDS_PREP_DTE," & _ "B.APVL_CDE," & _ "COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _ "COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _ "A.MSDS_CMDTY_VERIF, " & _ "A.CATALOG_ID " & _ "FROM ( MATEQUIP.VMSDS_CMDTY A " & _ " RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _ " ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _ " LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _ " ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _ " LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _ " ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) " SQL1 = "" SQL1 = SQL SQL = SQL & "WHERE " & Where & " " ================================== Here is a piece of the problem field, note repeating colors etc. CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11
View Replies !
Stop Inserting Duplicate Entries
Hi I am trying to insert entries in a table which has a composite primary key and i am inserting it on UID basis. INSERT INTO TABLE_B (TABLE_B_UID,NUM_MIN, NUM_MAX,BIN, REGN_CD, PROD_CD, CARD) (SELECT UID,LEFT(NUM_MIN,16),LEFT(NUM_MAX,16),BIN, REGN_CD, PROD_CD, CARD FROM TABLE_A WHERE UID NOT IN (SELECT TABLE_B_UID FROM TABLE B)) When i insert it tries to insert a duplicate entries and gives me an error. Since I am new to SQL SERVER 2000 i need some help. I tried IF NOT EXISTS, EXCEPT but i guess i am wrong at the syntax. Can anybody help me out?
View Replies !
Finding Duplicate Entries (with Different Keys)
Yet another simple query that is eluding me. I need to find records in a table that have the same first name and last name. Because the table has a primaty key, these people were entered twice or they share the same first and last name. How could you query this: ID fname lname 10001 Bill Jones 10002 Joe Smith 10003 Sue Jenkins 10004 John Sanders 10005 Joe Smith 10006 Harrold Simpson 10007 Sue Jenkins 10008 Sam Worden and get a result set of this: ID fname lname 10002 Joe Smith 10005 Joe Smith 10003 Sue Jenkins 10007 Sue Jenkins
View Replies !
Duplicate Entries In Returned SQL Field
Hi All ! Below is a snippet of MS SQL inside ASP that retieves Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get back green, red, blue, and yellow which is correct. On another similar search different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic Glass Sand. I want to remove the repeating elements returned in this field. I hope this makes sense. PS I tried to use distinct but with no luck I want just one of each in the example below. Thanks in Advance! Scott ============================== SQL = "" SQL = "SELECT B.CIMS_MSDS_NUM," & _ "A.COMMODITY_NUMBER, " & _ "B.CIMS_TRADE_NME," & _ "B.CIMS_MFR_NME," & _ "B.CIMS_MSDS_PREP_DTE," & _ "B.APVL_CDE," & _ "COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _ "COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _ "A.MSDS_CMDTY_VERIF, " & _ "A.CATALOG_ID " & _ "FROM ( MATEQUIP.VMSDS_CMDTY A " & _ " RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _ " ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _ " LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _ " ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _ " LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _ " ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) " SQL1 = "" SQL1 = SQL SQL = SQL & "WHERE " & Where & " " ================================== Here is a piece of the problem field, note repeating colors etc. CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS
View Replies !
Check For Duplicate Entries In One Field
Obviously, I'm a complete n00b at SQL. I have a table in Access 2003 with about 6,000 records and there are about 20 records that have duplicate data in the first field (CompID). I'm trying to make the first field my primary key, so I need to fix these duplicate entry. I could export to Excel and fix the problem that way, but in the interest of learning SQL I want to figure out how to do it properly. Thanks in advance for what is hopefully a simple answer.
View Replies !
Using Trigger/Stored Procedure (Delete, Insert)?
I have 3 tables...TableA, TableB, TableC TableA - Personal InformationPersonalInfoId (Primary) , First Name,Last NameTableB - Personal Information To Department IDReferenceID, FKPersonalInfoId, FKDepartmentIdTableC - DepartmentDepartmentId, DepartmentNameI am coding Asp.Net VB using VWD express with Sql Server Express. I know how to create a stored procedure to delete, insert and even update a record in TableA, TableB, TableC respectively.If I need to delete a record in TableC, which has a related record in TableB, I have read that I need to use a Trigger. I never have used a Trigger and it is new to me. Can someone point me a way on how to use one in this case of my deleting scenario. Pretty much, if a user clicks on a delete button, and deletes a record in my TableC, I dont want a FKDpartmentId in my TableB that doesnt exist anymore because it was deleted in TableC or prevent a user from deleting that record till the relationship in TableB is no longer valid. In the same vain, If I have a input form which ask the user to enter their First Name and Last Name and Department, i would like to add those records in TableA for First and Last Name, TableB for the Department. Once again, how do I create a Trigger that if I insert a record in Table A to also insert the information for Department in Table B, if its successful in my stored procedure. Hope that made sense.Thanks.
View Replies !
Best Method Of Checking For Duplicate Entries In SQL Server
Here is my situation. I have a table in my application that pairs users with cars they like. We'll call this table Favorites. A user can browse the site and they can designate as many cars they want as favorites. For example, a user can go to the Honda Accord page and add that as a favorite car and then go to the Toyota Camry page and add that as a favorite car. However, if he/she goes to that Honda Accord page and tries to click the "Add to Favorites" button again, at the present state of my application, it will just add another entry into the Favorites table with a duplicate pairing. So, if I were to datalist the table to generate a listing of all favorites belonging to a certain user, he/she may potentially be returned with superfluous duplicate entries. Not to mention, taking up valuable database space and not looking very professional. In my Favorites table, the 3 fields are.....favoriteId (set as primary key)userIdcarId I've been thinking about this for awhile and I've come up with 2 solutions. I'm a newbie to ASP.NET/programming so I don't have enough insight to make a decision or to even think up of other alternatives. 1) Check proactively by doing a.....SELECT favoriteID FROM Favorites WHERE userId = x and carId = y (where x and y are variables)If I get a null return, it means I can go ahead and let the user add the car as a favorite in the database. If I get a valid value, then it means there already exists the same pairing, so I exit out without updating the table. 2) Check reactively by forcing an exception whenever a user tries to enter a duplicate pairing. I'm not sure how to do this, but perhaps, instead of making "favoriteId" a primary key, perhaps, I can make a primary key pairing of "userId" and "carId". And by trying to do an insert with a primary key that already exists, we know it won't work since primary keys by definition are unique. Now, I expect some concurrent users on my site, so I must take into consideration pros and cons of each and determine which is more efficient. Checking proactively will force a check even if the table does not contain a duplicate pairing of user and car. However, having a duplicate primary key may be more expensive from a database point of view and may slow down lookups, etc. Or maybe neither has significant benefits, in which case, I rather go with proactive, since I've already coded it and it works fine. Or maybe there is a third alternative, which I did not think. Which method do programmers usually take and which is a better practice? TIA for your help.
View Replies !
Delete Trigger On Same Table
I have 4 colums in a table Project, Sections,Tasks,Subtasks Each subtask will haven a row. I need to write a trigger when I delete a task it needs to delete all the subtasks relating to it. When I delete a section it needs to delete all the tasks and subasks relating to it. similarly for project. This trigger for task-subtask works. CREATE TRIGGER "[Deletetasktrigger]" ON [Tbl] FOR DELETE AS SET NOCOUNT ON /* * CASCADE DELETES TO '[Tbl B]' */ DELETE [tbl] FROM deleted, [Tbl] WHERE deleted.[task] = [Tbl].[task] THis works fine. But when I do it for sections I get this error. "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" .. Help Please!!!!!!
View Replies !
Cascade Delete Trigger On Same Table
I have 4 colums in a table Project, Sections,Tasks,Subtasks Each subtask will haven a row. I need to write a trigger when I delete a task it needs to delete all the subtasks relating to it. When I delete a section it needs to delete all the tasks and subasks relating to it. similarly for project. This trigger for task-subtask works. CREATE TRIGGER "[Deletetasktrigger]" ON [Tbl] FOR DELETE AS SET NOCOUNT ON /* * CASCADE DELETES TO '[Tbl B]' */ DELETE [tbl] FROM deleted, [Tbl] WHERE deleted.[task] = [Tbl].[task] THis works fine. But when I do it for sections I get this error. "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" .. Help Please!!!!!!
View Replies !
Trigger Not Firing On Cascade Delete Table
I have three tables: BulkMemberHeader - which has a cascade delete on BulkMemberDetail of any related records BulkMemberDetail €“ which has a DELETE trigger which gets the member ID from deleted and deletes the member record from the member table Member This issue: > When I delete a record from BulkMemberDetail the trigger fires and deletes the record from the Member table as it should > If I delete a record from the BulkMemberHeader, all corresponding records in BulkMemberDetail are deleted, but the trigger to delete the record in the Member table does not seem to fire Is it a limitation on SQLServer 2000 that does not allow triggers to fire in a scenario like this? Any suggestions or comments would be great. Thanks, Mike
View Replies !
Writting A Table
Hello, I have to write a trigger to truncate at a scheduled time.Truncate statement is not allowed in the trigger.What should I do.I mean how to go for it. Thanks for any help. JAY
View Replies !
Delete Entries In Replication Monitor
Hi everybody, when I look into the replication monitor of our sql 2005 server I see a lot of subscriptions that are no longer used. I wrote a program for a smartphone using the sql compact edition (sql ce db). Just in case there are some problems i do not want to mentioned here, i use an preconfigured sql ce db, reinitialize it and afterwards synchronize it. Then the old subscription is no longer used. The problem is that up to now I did find a way to delete this unused subscriptions at the publisher database. As a hint: there is no way to get access to the old sql ce db for removement issues. The dbs are gone. So, is there a way to solve my problem? We are using sql 2005 server as publisher and are using merge replication. Thanks for help in advanced Garfield
View Replies !
VB6 && VB2005 Runtime Error When Writting To Access Database Table
In both VB6 and VB2005 Both are loaded on same computer. When attempting to write to an access database table using ADO I get a runtime error: -2147217887(80040e21) "mutiple-step operation generates errors. Check each status value. " Reading the database is no problem. I only get the errors when writting to database. I have used the following code forever in VB6 and never got this error. Now that I have Visual Studio 2005 loaded on the same machine. I get this error. I get the exact same error when using this code in VB2005. I know VB6 but am just learning 2005. The following code is writting to a database table from various user imputed text and combo boxes. I used basicly the same code in VB2005 and get the same exception error. This code works on machines that do not have VB .net loaded. How do I fix this. My application is useless unless the user can make changes to their data. Public Function ActionToDb() Dim noPo As Integer Dim SQL As String, rst As ADODB.Recordset, dataSourceName As String dataSourceName = DBPATH & "LVBURV Database.mdb" dbConn.ConnectionString = "DBQ=" & dataSourceName & ";Driver={Microsoft Access Driver (*.mdb)}" dbConn.Open On Error GoTo ErrorHandler Set rst = New ADODB.Recordset With rst SQL = "Select [mLVbuNo], [mStratInit], [mMerticLv],[mSubMetLv],[mTaskAction],[mImpact]," & _ "[mStatus], [mOwner], [mTitle],[mOrigDate],[mCurrentDate],[mCopmpleteDt],[mComment],[mSource]," & _ "[mStatColor],[mHyperlink],[CompCheck] From [tblMasterActionItem]" .CursorLocation = adUseClient .Open SQL, dbConn, adOpenKeyset, adLockBatchOptimistic, adCmdText noPo = .RecordCount 'If .RecordCount > 0 Then .MoveLast .AddNew rst.Fields("mLVbuNo") = giRecNo rst.Fields("mStratInit") = frmActionDe.cboStatInit.Text rst.Fields("mMerticLv") = frmActionDe.cboMetricLever.Text rst.Fields("mSubMetLv") = frmActionDe.cboSubMet.Text rst.Fields("mTaskAction") = frmActionDe.txtTask.Text rst.Fields("mImpact") = frmActionDe.txtImpact.Text rst.Fields("mStatus") = frmActionDe.txtStatus.Text rst.Fields("mOwner") = frmActionDe.cboOwn.Text rst.Fields("mTitle") = gstOwnT rst.Fields("mOrigDate") = frmActionDe.txtOrigDt.Text rst.Fields("mCurrentDate") = frmActionDe.txtCurDt.Text rst.Fields("mCopmpleteDt") = frmActionDe.txtCompDt.Text rst.Fields("mComment") = frmActionDe.txtComments.Text rst.Fields("mSource") = frmActionDe.cboSource.Text rst.Fields("mStatColor") = frmActionDe.txtStatColor.Text rst.Fields("mHyperlink") = frmActionDe.txtHyperlink.Text If optCompCheck.Value = True Then rst.Fields("CompCheck") = True Else rst.Fields("CompCheck") = False End If 'End If .Update .Close End With dbConn.Close ExitSub: On Error Resume Next dbConn.Close Set rst = Nothing Set dbConn = Nothing Exit Sub ErrorHandler: WriteToErrorLogEnd Err.Description & " in ActionToDb" Resume End Function
View Replies !
Finding Duplicate Entries In A &"smart&" Way - By Comparing First Two Words
What is the best way to compare two entries in a single table wherethe two fields are "almost" the same?For example, I would like to write a query that would compare thefirst two words in a "company" field. If they are the same, I wouldlike to output them.For example, "20th Century" and "20th Century Fox" in the companyfield would be the same.How do I do this? Do I need to use a cursor? Is it as simple as using"Like?"
View Replies !
SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!
I am having great difficulty with cascading deletes, delete triggers and referential integrity. The database is in First Normal Form. I have some tables that are child tables with two foreign keyes to two different parent tables, for example: Table A / Table B Table C / Table D So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D. SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D. Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D. When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers????? This is an example of my delete trigger: CREATE TRIGGER [DeleteA] ON A FOR DELETE AS Delete from B where MeetingID = ID; Delete from C where MeetingID = ID; And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first. So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table. So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required). Hope this makes sense... Thanks, Josh
View Replies !
Stored Procedure - Delete Some Rows (table)
How to I return the row count as result of deleting some rows from a table? This this script correct below?? Here's my stored procedure script.. Code: CREATE PROCEDURE dbo.sp_CleanUp_Table_tblSoldRaw /*************************************************************** ** CREATED: 12/04/2006 **************************************************************** ** DESCRIPTION: Clean the tblSoldRaw by deleting any data that ** is over 90 days old. We need keep the database ** as small as possible so the performance won't ** suffer on the server's and the client's machine. ** **************************************************************** ** NOTES: Hooked up to BookItOut Data Importer Windows Service **************************************************************** ** MODIFICATIONS: ** ** DATE WHO MODIFICATION DESCRIPTION ** ------------------------------------------------------------- ** ***************************************************************/ /*************************************************************** ** Uses: tblSoldRaw ***************************************************************/ AS DELETE FROM tblSoldRaw WHERE TIMESTAMP > (GETDATE() - 90) RETURN ROWCOUNT ERROR_HANDLER: RETURN -1 SET NOCOUNT OFF Thanks...
View Replies !
CREATE TABLE DUPLICATE OBJECT/DUPLICATE FIELD NAME ERROR Msg 2714
Hello Everyone: I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer. When I try to create the tables, for the query, I am getting the following error: Msg 2714, Level 16, State 4, Line 12 There is already an object named 'UserID' in the database. Msg 1750, Level 16, State 0, Line 12 Could not create constraint. See previous errors. I have duplicated this error with the following script: USE [testing] IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users1] CREATE TABLE [testing].[dbo].[users1] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users2] CREATE TABLE [testing].[dbo].[users2] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users3] CREATE TABLE [testing].[dbo].[users3] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database. I think that the schema is only allowing a single UserID primary key. How do I fix this? TIA
View Replies !
Soft Delete In Table, Why Merge Agent Report Hards Delete On Table ?
Hi seniors there are two tables involve in replication let say table1 and replicated table is also rep.table1. we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial. plz let me know where am i wrong and how i put it into right way. there is no triggers on published tables and noother trigger is created on published table. regards Ahmad Drshen
View Replies !
Delete Duplicate Records
I use a tabel for storin log data from a mail server. I noticed that I'm getting duplicate records, is there a way to delete the socond and/or third entry so I dont have any duplicates? I need this done in SP.
View Replies !
Delete Duplicate Rows
Suppose that we have the following rows in a MSSQL table : 1administrateur1NULLNULL 2administrateur2NULLNULL 1administrateur1NULLNULL 2administrateur2NULLNULL How to remove duplicates (leave only the 2 first rows) ?
View Replies !
Delete Duplicate Rows
Hi, I have the following query to select duplicate rows from the table. How can i delete them with out using temp table. select UserName, Title, Name, ColWidth, Sequence from table1 (nolock)) Group by UserName, Title, Name, ColWidth, Sequence Having count(*) >1 Any help would be greately appreciated. Thanks
View Replies !
DELETE DUPLICATE ROWS
CAN ANYBODY REPLY FOLLOWING QUESTIONS. I WANT TO DELETE DUPLICATE ROWS IN MY TABLE WITHOUT USING TRANSACTION TABLE. AND ONE MORE QUESTION HOW TO GET YESTERDAY DATE BY USING ISQL WINDOW. THANKS JK
View Replies !
Delete Duplicate Values Except One?
Hi Everybody, How to delete the duplicate values from the columns except one. For Eg. if the name of the table is "emp" with only one field "empname" of type varchar. This field has got four values all of which are duplicate. Eg. "samir" "samir" "samir" "samir" What I want is to delete 3 records and keep only one value. How do I achieve that? Regards, Samir.
View Replies !
Delete Duplicate Records
Sorry for the new thread. I have a userprofile table. There are a lot of duplicate records in this table. e.g. USERID-----LASTNAME---EMAILADDRESS----CREATEDATE ---------------------------------------------------------------------- 1----------A-----------A@yahoo.com---------2000-09-05 16:07:00.000 2----------A-----------A@yahoo.com---------2000-09-10 16:07:00.000 3----------A-----------A@yahoo.com---------2000-09-15 16:07:00.000 Userid is auto number, lastname and emailaddress are PK. I want to delete duplicate records. If lastname and emailaddress are the same, only keep a record which createdate is the most newest date. See above example I only want to the record which userid is 3. I have alreday created a code which I attached below. This code onle keep a record which userid is 1. Anybody can help me to solve this problem? Thanks. ============== My current code ==================== delete from userprofile where userprofile.userid in --list all rows that have duplicates (select p.userid from userprofile as p where exists (select lastname, emailaddress from userprofile where lastname = p.lastname and emailaddress = p.emailaddress group by lastname, emailaddress having count (userid)>1)) and userprofile.userid not in --list on row from each set of duplicate (select min(p.userid) from userprofile as p where exists (select lastname, emailaddress from userprofile where lastname = p.lastname and emailaddress = p.emailaddress group by lastname, emailaddress having count (userid)>1) group by lastname, emailaddress)
View Replies !
Delete Duplicate Rows
I have a table which looks as follow: field1 field2 field3 field4 field5 ...... A B C A X ...... A B C B Y ...... A B C C Z ...... A B C A Y ...... . . . . . ...... I want to delete all the rows except one row. Anybody can help? Thank you very much.
View Replies !
How Can I Delete All But One Instance Of A Duplicate Row?
I have several rows in a table that contain duplicate information. I want to trim them down to one instance per row, without other duplicates. I have several columns, but only 2 of the columns are used to determine the "Identical Rows", however one of the other columns could also be used to determine which of the others are deleted. Let's just say I have a table called DATA. In DATA I have the following columns: C1: ID/Key column (Unique numbers not associated with any other row) C2: Specific Data column 1 C3: Specific Data Column 2 C3: Keeper if not null C4: Misc Info C5: Misc Info Basically IF C2 & C3 are duplicated, then I want to keep one of these and ditch the others. The row I want to keep is the one that has info in C4, but at times C4 does not have anything in it on any of them, at that point I just care about keeping one of the C2 & C3 Duplicate rows. The other issue I face is C2 will always have something in it, but at times C3 will not and C4 will. So I want to keep the one duplicate row that has the most info in it. At minimum, I want to always keep C2 & C3 if they both contain data. For example, if I had the following duplicate rows: (C2) red (C3) 123 (C4) XYZ (C2) red (C3) 123 (C4) XYZ (C2) red (C3) 123 (C4) XYZ (C2) red (C3) (C4) XYZ (C2) red (C3) 123 (C4) I want to keep: (C2) red (C3) 123 (C4) XYZ I have some rows that have three exact duplicates, some that have four, some that have two. How can I tell the Query Manager to delete all but one instance of each duplicate row? I did do a query to find out how many duplicates I have and found it to be around 232,000+. Basically I will be cutting this in half if I can get rid of the duplicates. I have read 30 different ways on many sites and I am now officially confused Thank you for your help.
View Replies !
|
TRACKER
