Hi,
I have found that there are some duplicate rows in our ticket history table that was created due to legacy data import and then de-duping of customer records.
I am not sure what is the best way to find and delete these duplicate rows from the table. Please advice.
Hi Harpreet
We had a similar issue when we first went live with Tessitura, with duplicate logins, so I found this script on the web and amended it slightly for that purpose.
This script assumes that your ticket history table is named LT_TKT_HIST, and the id column is tck_no (which should contain the duplicate entries). If these are named differently you'll need to amend these entries.
I would suggest running this select statement first, as it will give you an idea of how many duplicates you should expect.
SELECT tck_no, Count(*) - 1 FROM LT_TKT_HIST GROUP BY tck_no HAVING Count(*) > 1
I'd also ensure you trial this in test a couple of times first, making sure it does what it should be doing. I simply ran this insert statement for my customer_no so I could add duplicates.
INSERT into lt_tkt_histselect * from lt_tkt_hist where customer_no = 1447753
These were then successfully deleted from the table using the script below.
Hope it helps.
Ben
/*
DECLARE @Count intDECLARE @tck_no int DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR SELECT tck_no, Count(*) - 1FROM LT_TKT_HIST GROUP BY tck_no HAVING Count(*) > 1OPEN dublicate_cursor FETCH NEXT FROM dublicate_cursor INTO @tck_no, @CountWHILE @@FETCH_STATUS = 0BEGINSET ROWCOUNT @CountDELETE FROM LT_TKT_HIST WHERE tck_no = @tck_noSET ROWCOUNT 0FETCH NEXT FROM dublicate_cursor INTO @tck_no, @CountENDCLOSE dublicate_cursor DEALLOCATE dublicate_cursor
*/
Hi Ben,
Thanks a lot for sending all the information. Just one more question when I have tried inserting duplicate row in lt_tkt_hist table I got an error “An explicit value for the identity column in table 'lt_tkt_hist' can only be specified when a column list is used and IDENTITY_INSERT is ON.”. Any advice on how to overcome this problem.
Thanks
Harpreet Gangyan | Database Analyst
Level 4 TSB Building| PO BOX 5143, Wellesley Street | Auckland 1411, New Zealand T: +64 9 357 3351 | F: +64 9 357 3359 | www.the-edge.co.nz
Please consider the environment before you print this email.
Notice: This email is confidential. If it is not intended for you please do not read, distribute or copy the email or any attachments. Please notify the sender that you have received the message in error and delete the original message and any attachments. Please note that any views expressed in this email may be those of the individual sender and may not necessarily reflect the views of THE EDGE®.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ben Magson Sent: Saturday, 16 May 2009 00:50 To: Harpreet Gangyan Subject: Re: [Tessitura Technical Forum] duplicate rows in ticket history table
INSERT into lt_tkt_hist select * from lt_tkt_hist where customer_no = 1447753
DECLARE @Count int DECLARE @tck_no int DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR SELECT tck_no, Count(*) - 1 FROM LT_TKT_HIST GROUP BY tck_no HAVING Count(*) > 1 OPEN dublicate_cursor FETCH NEXT FROM dublicate_cursor INTO @tck_no, @Count WHILE @@FETCH_STATUS = 0 BEGIN SET ROWCOUNT @Count DELETE FROM LT_TKT_HIST WHERE tck_no = @tck_no SET ROWCOUNT 0 FETCH NEXT FROM dublicate_cursor INTO @tck_no, @Count END CLOSE dublicate_cursor DEALLOCATE dublicate_cursor
From: Harpreet Gangyan <bounce-harpreetsinghgangyan5939@tessituranetwork.com> Sent: 5/14/2009 8:32:58 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
You'll need to turn the Identity off for the table's tck_no column.
* Right-click on the table, and select Design
* Highlight the column with the ID (tck_no or your equivalent)
* In the column properties expand 'Identity Specification'
* Beneath this, where it says '(Is Identity)', change the properties to No.
You should then be able to freely insert duplicate entries for existing tck_nos, and subsequently delete.
Only do this in TEST though!
Hope it all goes well.
Thanks, Ben
Thanks Ben.
I have successfully inserted the record and deleted it but realize that when I insert duplicates in LT_TKT_HIST it inserts the complete duplicate including same tck_no but my original problem is I have duplicates in my LT_TKT_HIST table but with different tck_no and don’t know how to identify those duplicates using query.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ben Magson Sent: Tuesday, 19 May 2009 21:32 To: Harpreet Gangyan Subject: Re: [Tessitura Technical Forum] RE: duplicate rows in ticket history table
From: Harpreet Gangyan <bounce-harpreetsinghgangyan5939@tessituranetwork.com> Sent: 5/17/2009 6:15:12 PM
Hi Harpreet,
There are another way to avoid dups.
1, create a tmp table #tm with a IDENTITY
2, insert source records into tmp table.
3, create another tmp table #tm1 with dups only.
4, insert into target table with records excluding items in #tm1.
----
create table #tm([id_key] [int] IDENTITY(1,1) NOT NULL,field1 int null,field2 varchar(50) null)
insert into #tm(field1,field2) select field1,field2 from tblSource
select a.id_key into #tm1 from #tm as a inner join tblTarget as b on a.field1=b.field1 and a.field2=b.field2
insert into tblTarget (field1,field2) select field1,field2 from #tm where id_key not in (select id_key from #tm1)
drop table #tm
drop table #tm1
---
(you can have only one tmp table, if you replace #tm1 with a selection)
have fun.