• Replies 5 replies
  • Subscribers 337 subscribers
  • Views 64 views
  • Users 0 members are here
Related

duplicate rows in ticket history table

Harpreet Gangyan
$organization

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.

Parents
  • $organization

    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_hist
    select * 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 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

    */

  • $organization in reply to Ben Magson

    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

    THE

    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

     

    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_hist
    select * 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 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

    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.




    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!

  • $organization in reply to Harpreet Gangyan

    Hi Harpreet

    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

  • $organization in reply to Ben Magson

    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.

    Thanks

    Harpreet Gangyan | Database Analyst

    THE

    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: Tuesday, 19 May 2009 21:32
    To: Harpreet Gangyan
    Subject: Re: [Tessitura Technical Forum] RE: duplicate rows in ticket history table

     

    Hi Harpreet

    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

    From: Harpreet Gangyan <bounce-harpreetsinghgangyan5939@tessituranetwork.com>
    Sent: 5/17/2009 6:15:12 PM

    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

    THE

    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

     

    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_hist
    select * 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 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

    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.




    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!




    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!

  • $organization in reply to Harpreet Gangyan

    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.

    • Reply
    • $organization in reply to Harpreet Gangyan

      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.

    Children
    No Data