Delete Duplicate Entries From Tables In My Database Using Query Analyzer
Hello,
How can I delete duplicate entries from tables in my database using Query Analyzer, as there are many duplicate entries in my tables, I want to delete them.
Thanks in advance,
Uday.
View Complete Forum Thread with Replies
Related Forum Messages:
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 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 !
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 !
Delete Duplicate Records From Huge Tables
Hi All,I've the following table with a PK defined on an IDENTITY column(INSERT_SEQ):CREATE TABLE MYDATA (MID NUMERIC(19,0) NOT NULL,MYVALUE FLOAT NOT NULL,TIMEKEY INTEGER NOT NULL,TIMEKEY_DTTM DATETIME NULL,IID NUMERIC(19,0) NOT NULL,EID NUMERIC(19,0) NOT NULL,INSERT_SEQ NUMERIC(19,0) IDENTITY(1,1) NOT NULL)GOALTER TABLE MYDATAADD CONSTRAINT PK_MYDATAPRIMARY KEY (INSERT_SEQ)GOThe TIMEKEY_DTTM field is generated, from the value actually insertedinto theTIMEKEY field, by the following trigger:CREATE TRIGGER TIMEKEY1ON MYDATAFOR INSERT ASBEGINDECLARE @M_TIMEKEY_DTTM DATETIMESELECT @M_TIMEKEY_DTTM = DATEADD(SECOND, INS.TIMEKEY +EP.GMT_OFFSET * 0 ,'1970-01-01 00:00:00.000')FROM INSERTED INS, LOCATIONINFO EPWHERE INS.EID = EP.EIDUPDATE MYDATASET TIMEKEY_DTTM = @M_TIMEKEY_DTTMFROM INSERTED INS, MYDATA MDWHERE MD.INSERT_SEQ = INS.INSERT_SEQENDGOThere is also a composite, non unique, index defined on thetuple:(MID,IID,TIMEKEY,EID)CREATE INDEX IX_METDATA ON MYDATA (MID,IID,TIMEKEY,EID)GOAs a consequence of an application design change, I would also changethis index to be UNIQUE, but when I try to drop and create it I get anerror, because the tables stores some duplicated rows...In order to succesfully upgrade the index definition, I wrote some DMLstaementsto lookup and remove the duplicated rows, keeping only the firstrecord inserted, i.e. the one with the lowest INSERT_SEQ:---- This table stores then umber of duplicated records eventuallydiscovered-- into the MYDATA table; the initial value for the NUM_DUPLICATESfield is-- 0 (no duplicated record)--DROP TABLE DUPLICATESGOCREATE TABLE DUPLICATES (TABLENAME VARCHAR(17),NUM_DUPLICATES NUMERIC(19,0) )GOINSERT INTO DUPLICATES VALUES ('MYDATA',0)GOINSERT INTO DUPLICATES VALUES ('CATEGORIESDATA',0)GO---- ///////// CLEAN UP OF MYDATA TABLE--DROP TABLE TMP_MYDATAGOCREATE TABLE TMP_MYDATA (MID NUMERIC(19,0) NOT NULL,TIMEKEY INTEGER NOT NULL,IID NUMERIC(19,0) NOT NULL,EID NUMERIC(19,0) NOT NULL,INSERT_SEQ NUMERIC(19,0) )GO---- Insert into the TMP_MYDATA table all the duplicated records for-- the tuple (MID,IID,TIMEKEY,EID) and NULL for the INSERT_SEQ field--INSERT INTO TMP_MYDATA (MID,IID,TIMEKEY,EID)SELECT MID,IID,TIMEKEY,EIDFROM MYDATAGROUP BY MID,IID,TIMEKEY,EIDHAVING COUNT(*)>1GO---- Updates the INSERT_SEQ field to the lowest value in the group-- of duplicated records--UPDATE TMP_MYDATASET TMP_MYDATA.INSERT_SEQ = (SELECT MIN(INSERT_SEQ)FROM MYDATAWHERE TMP_MYDATA.MID = MYDATA.MID ANDTMP_MYDATA.IID = MYDATA.IID ANDTMP_MYDATA.TIMEKEY = MYDATA.TIMEKEY ANDTMP_MYDATA.EID = MYDATA.EID )GO---- Updates the value of NUM_DUPLICATES for the MYDATA table.--UPDATE DUPLICATESSET NUM_DUPLICATES = (SELECT COUNT(*) FROM TMP_MYDATA)WHERE TABLENAME = 'MYDATA'GO---- Delete from the MYDATA table all the duplicated records,-- keeping only the row with the lowest INSERT_SEQ-- The delete is performed only if there are duplicated recors;-- this is achieved using a "short circuit" AND on the number ofrecords-- stored into the NUM_DUPLICATES field of the DUPLICATES table for-- the MYDATA table...--DELETE FROM MYDATAWHERE ( SELECT NUM_DUPLICATES FROM DUPLICATES WHERE TABLENAME ='MYDATA') > 0 ANDEXISTS ( SELECT 1FROM TMP_MYDATAWHERE MYDATA.MID = TMP_MYDATA.MID ANDMYDATA.IID = TMP_MYDATA.IID ANDMYDATA.TIMEKEY = TMP_MYDATA.TIMEKEY ANDMYDATA.EID = TMP_MYDATA.EID ANDMYDATA.INSERT_SEQ > TMP_MYDATA.INSERT_SEQ )GOThis tecnique works fine on a normal table (1M recs) but is not veryperformanton huge tables (>10M records)!Do you know a better way to achieve the task of removing all theduplicates records, preserving the lowest INSERT_SEQ betwee theduplicates and also preserving the sequence seed, so that a new recordinserted at time t1>t0 is enumerated with an INSERT_SEQ|t1 >max(INSERT_SEQ)|t0 ?Thanks a lot for your help!PatrizioPS. sorry for such a large post!
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 !
Unable To Delete Duplicate Records In Database
Hi,I have an sql database that has the primary key set to three fields,but has not been set as unique(I didn't create the table).I have 1 record that has 2 duplicates and I am unable to delete theduplicate entries.If I try to delete any of the three records(they are identical) I getthe message 'key column is insufficient or incorrect. Too many rowswere affected by update'.I am trying to do this within Enterprise Mgr.Any suggestion?Thanks much
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 !
Query Analyzer --Temp Tables
Is there a quick way to create a temp table that is the same as an existing table in the schema? I am used to Informix where "select * from <table A> into temp B" would create the temp table B with the same schema is table A. Is this same functionality available with MS SQL server using query analyzer?
View Replies !
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 !
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 !
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 !
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 !
I Use SQL 2000, Can You Use One Delete Query To Delete 2 Tables?
this is my Delete Query NO 1 alter table ZT_Master disable trigger All Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) alter table ZT_Master enable trigger All I have troble in Delete Query No 2 here is a select statemnt , I need to delete them select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) I tried modified it as below delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) but this doesn't works.. can you please help? and can I combine these 2 SQL Query into one Sql Query? thank you
View Replies !
Query Highest Entries From Database - Pls Read More.....
Hi folks, sorry for the poor explanation. Im using SQL 2000 I have a database that has a column named 'Initials' in a char in field I want to be able to return in a query the highest entries if an indiviuals initials & count from the table, so it would display some like this Initials Count DRT 51 AMS 49 JJJ 21 PLI 10 Hope u can help, thanks in advance
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 !
Creating Tables In SQL Server Via Query Analyzer With Relationships
I've been searching around for some info on how to set this up, but with no luck.I need to have a .sql file that will set up a few tables and these tables will have relationships and contraints.I can do this by hand in enterprise manager, but need to set up some procedures that will do the same thing.For instance, I can create the tables just fine.....CREATE TABLE students ( sId int NOT NULL PRIMARY KEY, studentId varchar(50) NOT NULL, course varchar(50) ) CREATE TABLE courses ( cId int NOT NULL PRIMARY KEY, course varchar(50) NOT NULL, sco varchar(50) )But, I need to set up relationships in there somehow.Once student may have many courses (one to many) and one course may have many sco's (one to many) SCO would be another table.Can someone point me to a good link that would show how to complete these procedures?Thanks all,Zath
View Replies !
Read Ms-Access Tables From Query Analyzer - MSSQL
Actually, I'm trying to develop automated system in VB6 which is read all the data from Ms-Access tables and dump in Ms-SQL server tables, I'm wondering what will be the best way of doing that I heard that in Query Analyzer can I able to see Ms-Access tables ? I appreciate if you pls solve my puzzle..... thanks a lot.
View Replies !
Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database
From Newbie to Newbie, Add reference to: 'Microsoft ActiveX Data Objects 2.8 Library 'Microsoft ADO Ext.2.8 for DDL and Security 'Microsoft Jet and Replication Objects 2.6 Library -------------------------------------------------------- Imports System.IO Imports System.IO.File Code Snippet 'BACKUP DATABASE Public Shared Sub Restart() End Sub 'You have to have a BackUps folder included into your release! Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb" 'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & "" Try File.Copy(z, f) Catch ex As System.Exception System.Windows.Forms.MessageBox.Show(ex.Message) End Try MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub Code Snippet 'RESTORE DATABASE Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName 'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With 'Notify user MsgBox("Data restored successfully") Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub Code Snippet 'CREATE NEW DATABASE Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5") Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table 'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet OLEDB:Engine Type=5") 'Open the Catalog Cat.ActiveConnection = Cn 'Create the table (you can name it anyway you want) Tablename.Name = "Table1" 'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID") 'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8) 'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename) 'User notification ) MsgBox("A new empty database was created successfully") 'clean up objects Tablename = Nothing Cat = Nothing Cn.Close() Cn = Nothing 'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub Code Snippet 'COMPACT DATABASE Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine 'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5") 'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") 'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb") 'User notification MsgBox("The database was compacted successfully") End Sub End Class
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 !
Delete From 2 Tables With 1 SQL Query
I have 2 tables that are joined together by a primary key (Order Number). Can I use one SQL query to delete from both of the tables. One table contains the order information from a client (Order Number, Customer Name etc). The other table has order information (Order Number, Item Number, Quantity Ordered etc.) I need one statement that will allow me to remove the items from both tables. Can this be done. Thanks in Advance Wes
View Replies !
HELP - Exporting Database Without DTS / Query Analyzer
Dear SQL, I have an MS SQL account somewhere on the net and I had problems using DTS for import/export data as OBJECTs (which including things like IDENTITY fields and Primary key...) Though SP3 is updated on both edges - I just can get a working solution, I'm was trying to export Table data & schema using an amazing Tool I found: (WebDataAdmin.msi) But it still gives me hard time on NTEXT fields that has UNICODE (UTF-8) characters so the import can NOT work (unrecognized characters...) Finally: ~~~~~ 1. Do U know any other tool like this one that allow exporting data (also with UNICODE characters) 2. Do U have an idea how can I fix this (great looking) Tool from Microsoft maybe I can tell it to create a UTF-8 file instead of just ANSI file ? (HOW ?)
View Replies !
Trying To Select AdventureWorks Database Data From Query Analyzer - Need Help
Hi there, I have installed Sql server 2005 developer on my machine which already has a Sql server 2000 installed on. Now i am trying to query the Sqlserver 2005 data(Ex: from Person.Address located in AdventureWorks database) in Sqlserver 2000 query analyzer: When i try as Select * from Address it said invalid object. But when i explored AdventureWorks database in the Management studio i see it as Person.Address!!! i don't understand what is a Person in Person.Address??? any clues on this? So as a test when i ran select * from Person.Address it did work and i saw the results in query analyzer. Can any one help me understand about this confusion? Thanks-L
View Replies !
Can I Find The Space Allocated For A Database W/ Query Analyzer Using 7.0?
My group is trying to ensure that there is a sufficient amount of cushion between the space allocated and the current size of a database. I know that I can check this using the Enterprise Manager. Is there a stored procedure or a systems table that holds this information. I know that sp_spaceused will give me the unallocated space, but i want the allocated space. Any suggestions?
View Replies !
Viewing Database Via Query Analyzer And/or Enterprise Manager
We are running SQL 2000 SP3 on a Win2K server with multiple user databases. Our current problem is the ability of users to view the database objects via the native tools. Example: User Bob Bob has the following rights: DB A – datareader/datawriter DB B – datareader/datawriter DB C – datareader DB D – datareader DB E – datareader/datawriter The user has no builder rights or server roles. In Enterprise Manager, the user can not see any databases. In Query Analyzer, the user can only see DB A, B, and C. Anyone seen this problem before and/or have suggestions? Thanks in advance!
View Replies !
Delete All Tables From MS Access Database
HI I want to delete all tables from an MS Access database. i cannot use the designer . i have to do it thru an sql statement a bunch of statements will also do . . any body has a solution ?? P.s: All replies will be appreacited
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 !
How To Delete Unwanted Data From Multiple Different Tables With One Single SQL Query?
This a microsoft SQL 2000 server. I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID. Here is my problem: Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'. I know how to get the user table names from the DB as follows: SELECT name FROM dbo.sysobjects WHERE xtype = 'U' and name like 'Elect_Sub%Daily' What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18". delete from tableName where Date_Stamp = '2008-3-18'
View Replies !
IID_IDBDataSourceAdmin Error Trying To Create A Database Using Query Analyzer On A Mobile Device
Hi, Please provide some help regarding the "Interface Defining Error: IID_IDBDataSourceAdmin" error while trying to create a SDF database using Query Analyzer on a Windows CE 5.0 mobile device (Symbol MC3000). Error: 0x80004005 E_FAIL Native Error: 28558 Description: SQL Mobile encountered problems when creating database [,,,,] Param. 0: 0 Param. 1: 0 Param. 2: 0 Param. 3: Param. 4: Param. 5: A list of (related) installed packages: NETCFv2.wce5.armv4i.cab sqlce30.dev.ENU.wce5.armv4i.CAB sqlce30.repl.wce5.armv4i.CAB sqlce30.wce5.armv4i.CAB PS. Basically I have developed a mobile application that programmatically creates the database, the code worked on a similar device (Win CE 50), trying to run the application on a new device resulted in database creation errors. I tried creating a test database manually .. and this is what I got. Browsing MSDN or searching on the Forum did not help. ~Zarko Gajic
View Replies !
|
TRACKER
