Remove Duplicate Data
I have a query that for one reason or another produces duplicate information in the result set. I have tried using DISTINCT and GROUP BY to remove the duplicates but because of the nature of the data I cannot get this to work, here is an example fo the data I am working with
ID Name Add1 Add2
1 Matt 16 Nowhere St Glasgow
1 Matt 16 Nowhere St Glasgow, Scotland
2 Jim 23 Blue St G65 TX
3 Bill 45 Red St
3 Bill 45 red St London
The problem is that a user can have one or more addresses!! I would like to be able to remove the duplicates by keeping the first duplicate ID that appears and getting rid of the second one. Any ideas?
Cheers
View Complete Forum Thread with Replies
Related Forum Messages:
Dynamiclly Remove Duplicate Rows From Results Table Based On Column Data?
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them. each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field. Ideas? Thanks, Ad.
View Replies !
Remove Duplicate
HI All, I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates.. Thnx ;) Shani
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 !
REMOVE DUPLICATE ROWS
Hi everyone.How can I get the unique row from a table which contains multiple rowsthat have exactly the same values.example:create table test (c1 as smallint,c2 as smallint,c3 as smallint )insert into test values (1,2,3)insert into test values (1,2,3)i want to remove whichever of the rows but I want to retain a singlerow.TIADiego
View Replies !
Remove Duplicate Rows
I've got the following table data:116525.99116520.14129965.03129960.12129967.00And I need to write a query to return only rows 2 and 4, since theremaining rows have duplicate IDs. I've tried the Group By, but amhaving no luck.Thanks!
View Replies !
Need To Remove All Duplicate Records.
Hi I have a data in one table like below. EDITION PRODUCT INSERTDATE ---------- ------------ ---------------------- CNE TN-Town News 12/19/2007 12:00:00 AM TN TN-Town News 12/19/2007 12:00:00 AM What i have to do is if there are multiple records for one product in any day, then i need to remove all those records. In this case i am getting two records for the PRODUCT 'TN-Town News' and for INSERTDATE = 12/19/2007 . So i need to remove these two records from the table. How to do that?. Can anybody help me? Thanks Venkat
View Replies !
Remove Duplicate Rows
Hi Folks: I'm in the process of data conversion. Before bcp to the actual table, I create another intermediate table with same record structure, different name and without any indexes,foreign key constraint .... so that I can bcp the ASCII data set into that table with no error. Now, is it possible to use one SQL statement to remove ALL DUPLICATE ROWS BUT THE LAST ONE. I meant, say that table t has 10 duplicate records on that fiel F1, I want to delete 9 of them but keep only one record only? Thanks in advance for your time and advice. David Nguyen
View Replies !
Remove Duplicate Records
hi, I have a table contains 3000 records, I ran this statement select company_name,count(*) company_name from vendor group by company_name having count(company_name)>1 This got me all companies and the duplicate counts, total duplicate counts were 80. I need to remove the duplicate and keep half of thoes companies... how can I do so, please hlep Thanks Ahmed
View Replies !
How To Remove Duplicate From CSV Using SSIS
Hi All , I have a CSV file which contains some duplicate record and i have to load this file in SQL server database using SSIS package . What i have to do is read the file and if the same record entry is occur more than 10 times for a particular unique combination ( like ID , Date , Time ) then i need to take only one record for that occurance. Plesae suggest , Help , Regards, Ashish
View Replies !
Faster Remove Duplicate SQL
I have a table containing over 100,000 email addresses. This email table gets duplicates in it, and our customers don't want a second (or third or fourth) copy of our news letter. To prevent this, we run the following SQL to kill the duplicates: Code Snippet DELETE FROM _email WHERE _email.eid IN ( SELECT tbl1.eid FROM _email AS tbl1 WHERE Exists ( SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1 ) ) AND _email.eid NOT IN ( SELECT Min(eid) FROM _email AS tbl1 WHERE Exists ( SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1 ) GROUP BY emailaddress ); This query takes about 2hrs to run which is really hurting our server preformance. Is there any way to do this faster? I am running SQL Server 2000 Thanks in advance
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 !
Remove Duplicate Rows From Table
I have a table with one column, and i want to remove those records from the table which are duplicate i meant if i have a records rakesh in table two time then one records should be remove... my tables is like that Names ------------ Rakesh Rakesh Rakesh Kumar Sharma Rakesh Kumar Sharma Baburaj Raghu Raghu and Output of query should be like that Names ----------- Rakesh Rakesh Kumar Sharma Baburaj Raghu Thanks in advance
View Replies !
Remove Duplicate Records From A Tble
hi, I run this script and found duplicate records. how can I delete all rows that have more than one record but Still keep one from each duplicated record SELECT SALES_CITY,ORDER_NO,CIRCUIT_ID ,COUNT(*) as countrows FROM TCOMS_ACC_WEEKLY GROUP BY SALES_CITY,ORDER_NO,CIRCUIT_ID HAVING COUNT(*) >1 SALES_CITY ORDER_NO CIRCUIT_ID COUNTROWS ---------- -------- -------------- ----------- alb C0000322 3ma04 a12 0001 3 alb C0000398 13a04 a04 0001 2 alb C0000398 13a04 a04 0002 2 alb C0000398 13a04 a04 0003 2 I got 1717 row(s) duplicate, I need to keep only one record from each duplicate. so I can create a primary key on( SALES_CITY,ORDER_NO,CIRCUIT_ID )after I delete the duplicate. thanks for your help Ahmed
View Replies !
Remove Duplicate Rows From A Table
Hi guys I have been using SQL server 2005. I have got a huge table with about 1 million rows. Problem is this table has got duplicate rows in lot of places. I need to remove the these duplicates. Is there an easy way to do that?? Is there a query in SQL to remove duplicate rows??? thanks Mita
View Replies !
Filtering/Grouping To Remove Duplicate Values...
There's some SQL below (T-SQL) & I'm wanting to have this result set grouped by Venue_ID in order to remove rows where there are duplicate values contained in just one column. The columns BCOM_ID contain unique values, but Venue_ID can have duplicate values. I only want to get rows for one instance of the Venue_ID (per BCOM_ID) - doesn't matter which instance but basically, no duplicates. Oh yes, one of the columns is a Bit column. Any ideas would be welcome & appreciated! Many thanks, Darren darren@darrenbrook.fsnet.co.uk SQL:- SELECT Booking_Header.BH_ID, Booking_Header.Booking_Header_Description, Booking_Header.BStat_ID, Booking_Header.BT_ID, Booking_Header.Tagged, Booking_Header.Status_Timestamp, Booking_Header.Start_Date, Booking_Header.Days_Qty, Proposal.PPL_ID, Proposal.PPL_Status, Booking_Component.BCOM_ID, Booking_Component.Component_Description, Booking_Component.Venue_ID, Venue.Venue_Code, Venue.Description, Address.Address_ID, Address.Town, Booking_Status.BStat_Description, Booking_Type.Type_Description FROM dbo.Booking_Header INNER JOIN dbo.Proposal ON dbo.Booking_Header.BH_ID = dbo.Proposal.BH_ID INNER JOIN dbo.Booking_Component ON dbo.Proposal.PPL_ID = dbo.Booking_Component.PPL_ID INNER JOIN dbo.Venue ON dbo.Booking_Component.Venue_ID = dbo.Venue.VE_ID INNER JOIN dbo.Address ON dbo.Venue.VE_ID = dbo.Address.VE_ID INNER JOIN dbo.Booking_Status ON dbo.Booking_Header.BStat_ID = dbo.Booking_Status.BStat_ID INNER JOIN dbo.Booking_Type ON dbo.Booking_Header.BT_ID = dbo.Booking_Type.BT_ID WHERE (dbo.Proposal.PPL_Status = 1) AND (dbo.Booking_Header.BH_ID = 10) Thanks, Darren
View Replies !
How To Remove Duplicate Rows From Full Join Query
I have 4 tables (SqlServer2000/2005). In the select query, I have FULL JOINED all the four tables A,B,C,D as I want all the data. The result is as sorted by DDATE desc:- AID BID BNAME DDATE DAUTHOR 1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com 1 1 abcxyz 2008-01-20 23:41:52.970 a@b.com 1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com 1 2 xyzabc 2008-01-20 23:43:17.110 a@b.com 1 2 xyzabc 2008-01-20 23:42:43.937 a@b.com 1 2 xyzabc NULL NULL 2 3 pqrlmn NULL NULL 2 4 cdefgh NULL NULL Now, I want unique rows from the above result set like :- AID BID BNAME DDATE DAUTHOR 1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com 1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com 2 3 pqrlmn NULL NULL 2 4 cdefgh NULL NULL I want to remove the duplicate rows and show only the unique rows but contains all the data from the first table A. I have to bind this result set to a nested GridView.
View Replies !
How To Remove Partially Duplicate Rows From Select Query's Result Set (DB Schema Provided And Query Provided).
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate ORDER BY t.topicid ASC,th.lastpostdate DESC Whose result set is as below:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 1 Java Overall a@b.com 2008-01-27 14:44:29.000 2 1 2 JSP NULL NULL 0 1 3 EJB NULL NULL 0 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 1 5 AWT NULL NULL 0 1 6 Web Services NULL NULL 0 1 7 JMS NULL NULL 0 1 8 XML,HTML NULL NULL 0 1 9 Javascript NULL NULL 0 2 10 Oracle NULL NULL 0 2 11 Sql Server NULL NULL 0 2 12 MySQL NULL NULL 0 3 13 CSS NULL NULL 0 3 14 FLASH/DHTLML NULL NULL 0 4 15 Best Practices NULL NULL 0 4 16 Longue NULL NULL 0 5 17 General NULL NULL 0 On modifying the query to:- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid) ORDER BY t.topicid ASC,th.lastpostdate DESC I get the result set as below:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 I want the result set as follows:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 2 JSP NULL NULL 0 1 3 EJB NULL NULL 0 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 1 5 AWT NULL NULL 0 1 6 Web Services NULL NULL 0 1 7 JMS NULL NULL 0 1 8 XML,HTML NULL NULL 0 1 9 Javascript NULL NULL 0 2 10 Oracle NULL NULL 0 2 11 Sql Server NULL NULL 0 2 12 MySQL NULL NULL 0 3 13 CSS NULL NULL 0 3 14 FLASH/DHTLML NULL NULL 0 4 15 Best Practices NULL NULL 0 4 16 Longue NULL NULL 0 5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
View Replies !
Delete Rows With Duplicate Column Data But Unique Row Data
Hello, This probably has been addressed before but I was unable to get the search to work properly on this site. I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example : Row 1 Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc. Row 2 Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc. Row3 Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc. Row 4 Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc. Etc. Etc. I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique. I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks, Kerry
View Replies !
How Do I Clean Up The SQL Server (ctp) From ADD/REMOVE Program Without The Change/remove Button
I have uninstalled the CTP version of the SQL Server express so that I can install the released version but CTP version is still listed in the add/remove program list but without the change/remove button. I have been to different sites to find information on cleaning this up and I have ran all the uninstall tool I can find but the problem still prevails. I cannot install the released version without completely getting rid of the CTP version. Please help anyone. Thanks deebeez1
View Replies !
Remove Duplicae Data
Hi All, I have information as following: Name Age ID Frew 11 3333333 Freni 23 3333323 Frew 11 3333333 As you can see first row and third row contain same record, how could I remove it? Thanks
View Replies !
How To Remove A Charater From A SQL Data.
Dear Frineds I have a hyperlink datatype column in Access database.I import that column into SQL Table. Now my column in sql table say 'imp_cl 'have all data from Acces table. But all record are prefix with"#" and at the end of value also have "#". that means it imported in following way eg. #//Server/image/img1.pdf# Now I want to remove this # from both side.As we have around 80,000 record of same type,it is very difficult to do it record by record. I would like to know aay fuction ,method or programme to remove this "#" from all record. Thannk you graceson
View Replies !
InStr To Remove : And , And Sum Data
Greetings All I have some data-- specifically times for cell phone usage in the format of (7:00, 15:51, 1,200:45, etc). I need to find a way to remove the ":" and the ","-- sum the data and then return it to its previous format of (7:00, 15:51, 1,200:45, etc). Does anyone have some code they could post?? thanks as always km
View Replies !
How To Remove {CR}{LF} And Tab {t} Characters From Source Data?
I have built a SSIS package that reads in data from a SQL Server 2005 source database into a flat file destination. The Row Delimiter is {CR}{LF}. The Column Delimiter is Tab {t}. The data being read from the SQL Server database contains both {CR}{LF} and Tab {t} characters in various fields on several rows. How can I process the input data from the SQL Server to remove these characters before passing it to the destination output file? Sorry if this is obvious to all, but I am only just starting with SSIS... Many thanks Adrian
View Replies !
How To Remove Repetitive Data Rows?
Hi, i am very struggle to find out. My input table consist of TWO columns. And this is my table (INPUT TABLE) Time Action 17:42 SELL 17:43 BUY 17:44 SELL 17:45 SELL 17:46 SELL 17:47 BUY 17:48 BUY 17:49 SELL 17:50 SELL When ever ACTION columns data are repeating remove that repetitive data rows. (i mean to say Bold characters rows want to remove) And finally my output should like this (OUTPUT TABLE) Time Action 17:42 SELL 17:43 BUY 17:44 SELL 17:47 BUY 17:49 SELL
View Replies !
Can I Duplicate Data?
Hello guys! Is it possible to duplicate a primary key? I would like my database to accept data with the same primary key. Is it possible? How do you declare ON DUPLICATE KEY UPDATE? Please help me. Thanks in advance.
View Replies !
Duplicate A Row Of Data MS Sql
I want to be able to duplicate a row of data in sql....Does anyone know if there is a sql command that will do that. I have a table with an auto increment primary key and I want to duplicate everything except the key into a new record. Thanks.
View Replies !
Duplicate Data
Hello all, I have recently been working on a project that requires one simple table to insert data into. The problem here is that all the data inserted must only access the database via stored procedure and I want to ensure that no duplicate data is inserted in the database. I have done quite a bit of research for many ways to perform duplicate data testing from building temp tables and on, but nothing has really stood out to me yet. I would really like to find some information on how to perform duplicate data testing using a stored procedure that allows to test the data being inserted before it is saved to the database; therefore, when the user inserts the fields and clicks the insert button, the fields will be tested against the existing data (via stored procedure) within the database before being added. Can anyone help? Thanks
View Replies !
Duplicate Data?
I am new to SQL server 6.5. I will need to stress test a sql server 6.5 test database by duplicating data. To do so, I need to know how to modify the primary key(which are numbers in character data type) to duplicate the data several times over. The primary key is character data. What I have done ion the past is use insert statments -let's say 20 -- and then copy them and change the date in a text editor and change the primary key column and the other coluimns with thea replace of different letters and numbers. This is slow and tedious. Does any one have a script I can run to do so? Can I do this sql or do I need some sort of stored procedure? Any help would be appreciated. Thanks. DAvid Spaisman would be greatly appreciated. THanks. David Spaisman
View Replies !
How To Sum Data That Is A Duplicate
Hello people, not sure how to do this. I have a unique Claim ID and when joined with the code table has 4 different Code ID and the Claim Amt is also unique to the Claim ID (One Claim Amnt per one Claim ID). I need all of these data for the report my problem is in getting the correct summation of the claim amt since this looks like I have 4 amounts of $1773.31 when in actuality there is only one. The Code ID is also part of the report parameter so I tried assigining the $ amount to just one of the Code ID but that will not work incase that particular Code ID is not selected in the parameter, the claim would read as a zero. Any help would be appreciated. Thanks Claim ID: Code ID Claim Amt 07089000296 757.39 1773.31 07089000296 V05.3 1773.31 07089000296 V30.01 1773.31 07089000296 V72.19 1773.31
View Replies !
Duplicate (almost) Data
Ok, I'm fairly new to SQL so sorry if this is obvious. I have two Databases on the same SQL server, and I want to duplicate a table from one in the other, but with two requirements. 1. Ideally I would like any changes in the data in the first to be automatically updated in the second. 2. I need to change the data type of the primary key from Bigint to Float in the process. I tried a DTS package, which created the second table fine, the problem I have is making a dynamic link. I have also tried a CREATE VIEW with both CAST and CONVERT in the first Db but to no avail. (This is all so I can link to an Access front end and it can't handle Bigint in the PK, I just get #Deleted in every cell.) Any thought? It seems as though it should be easy but I can't figure it.
View Replies !
Not Having Duplicate Data
I have a table called emails with a field named emailaddress and some emailaddress are entered more than once. I want to be able to list all emailaddress just once. is there an sql statement that I could use to generate this.
View Replies !
Programmatically Remove Varbinary Data From Table
Hey all, I have a profile table for members of a website that allows them to upload an avatar for use throughout the site. I decided to add the option to just remove the avatar if they want to no longer use one at all and I am stuck. I am running an update statement against thier entire profile incase they change more then just removing thier avatar but of course when I try this statement. myCommand.Parameters.AddWithValue("@avatar", "") I get a conversion error, which makes sence. So what instead of "" do I need for code to just insert empty data to overwirte thier current avatar data. Any help would be great, thanks for you time.
View Replies !
Unable To Remove SQL Instance In Add/Remove Program
I need help, I am having a hard time removing my SQL instance inside the Add/Remove program. After i select the SQL Instance name and then I tried to remove it but it won't allow me to delete it. There isn't any error message or whatsoever. Actually, when i try to log it in my SQL Management studio, that certain sql instance name is not existing according to the message box. Is there any way to remove the Sql Instance in my system? I appreciate your help, Thanks IS Support
View Replies !
Inserting Duplicate Data
This is probably a silly question to most of you, but I'm in the processof splitting off years from a large DB to several smaller ones. Some ofthe existing smaller DBs already have most of the data for theirrespective years. But some of the same data is also on the source DB.If I simply do an insert keying on the year column, and a row beinginserted from the source DB already exists in the target DB, will aduplicate row be created?And if so, how can I avoid that?Thanks,John Steen*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Inner Join Duplicate Data
i have 2 tables not connected in any way but both have orderid filed (same filed). In one table this filed (and onther one) are keys, the second table dose not hace a key at all. The same order_id CAN repeat itself in each table. When i try to join the tables (some rows just in one table and some in both): Select tab1.name, tab1.orderid, tab2.sku from tab1 inner join tab2 on tab1.orderid=tab2.orderid The result i get is duplicate. each row is multiple. What I'm doing wrong?
View Replies !
Duplicate Data In Column
I know that I have duplicate data in a column. The column has over 40,000 rows so manual checking would not be feasible. I don't think the whole row is the same, but I know for a fact that one particular column has at least 2 rows with the same data. Help please!
View Replies !
Identifying Duplicate Data
Hi everybody, I'm migrating a table that has above 20,000 records and lot of duplication.Let's say an Employee table with multiple records having slight diference in the EmployeeName field.Now nobody would like to sit and manually identify them with such hugh number of records. Is there any way which would help me identify most of them and reduce the redundancy. Thanx Aby...
View Replies !
Filtering On Duplicate Data
Hello, I have a dataset which I would like to remove data from, but I can't seem to find out how to do this. The dataset contains the following columns: SCode, NIn, SIn, AIn, NOut, SOut, AOut and TagNumber. I would like to remove data from the dataset when the following occurs: ("SC123", "NIn123", "s-in-323", "a-in-342", "NOut43", "s-out-231", "a-out-45", "tagnumber12") ("SC123", "NIn123", "s-in-xyz", "a-in-xws", NULL, NULL, NULL, "tagnumber12") This is when NIn occurs with the same value more than once, and I would like to remove (or ignore, filter) the row when NOut, SOut and AOut are null. I am new to SSIS and can't see how I could do this (although I'm sure it's possible). If anyone could show me how I would appreciate it. Thanks.
View Replies !
How To Eliminate Duplicate Data
I have a table with 68 columns. If all the columns hold the same value except for one which is a datetime column I want to delete all but one of the duplicate rows. Preferably the latest one but that is not important. Can someone show me how to accomplish this?
View Replies !
Displaying Duplicate Data
Hi. Not sure which section this request needs to be put in, but i'm relatively new to SQL. I have 1 table which contains an user_id (autonumber), user_name, and user_profile. I have 2 other tables: config_version (cv) and config (c) These two tables both access the user table (us) to view the user_id (which is required). I want to be able to view the user_names for both "cv" and "c" on a seperate page. Using the code below, i'm lost. I created what i wanted in MS Access with the use of a 2nd table (this might be easier to understand than my rant above). However, I don't want a 2nd table. Can someone provide me with a function, or the "answer" to my problem? Highlighted Blue - just there to fill in the front page with data (not wanted) Highlighted Green - doesn't work, but was my first attempt Code: public function ShowQuotes Dim objConn Dim objADORS Dim strSQL Dim row Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = strConn objConn.Open Set objADORS = CreateObject("ADODB.RecordSet") strSQL = "Select top 50 " strSQL = strSQL & " cv.config_version_id as cvid, cv.configuration_id as cid, cv.version_number as cnum" strSQL = strSQL & ", cv.status as cstat, cv.total_price as cprice, cv.modification_date as cmod, cv.version_description as cvrem" strSQL = strSQL & ", c.remarks as qrem" strSQL = strSQL & ", p.prod_description as pdesc, p.version as pvers, p.status as pstat" strSQL = strSQL & ", cust.customer_name as custname" strSQL = strSQL & ", us.user_nt_login as modname" strSQL = strSQL & ", us.user_name as usname" ' strSQL = strSQL & ", cv.user_id as modname" strSQL = strSQL & " from tbl_config_version as cv, tbl_configuration as c, tbl_product as p, tbl_user as us, tbl_customer as cust" strSQL = strSQL & strWhere 'SETS RESULTS TO BE UNIQUE strSQL = strSQL & " and us.user_id = c.user_id" strSQL = strSQL & " and cv.configuration_id = c.configuration_id" strSQL = strSQL & " and c.product_id = p.product_id" strSQL = strSQL & " and c.customer_id = cust.customer_id" strSQL = strSQL & strOrderBy & ";" Image: www.mcdcs.co.uk/TT.jpg
View Replies !
Cann't Remove Data File From User Database
We are running SQL server 2003 with SP3. I'm trying to shrink a data files with the emptyfile option so I can eventually remove the file using the alter database command. However, I get the following error message when I run the alter database command: Error: the file PRADATA4 cannot be removed because it is not empty. The file that I'm trying to remove still has 62 extents on it. I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3. commands that I'm running are as follows: 1) USE PRA DBCC TRACEON(8901) DBCC SHRINKFILE ('PRADATA4', EMPTYFILE) DBCC TRACEOFF(8901) 2) USE PRA GO ALTERDATABASE PRA REMOVE FILE PRADATA4 GO Can anyone help? Thanks
View Replies !
|
TRACKER
