SQL : Delete duplicate record based on the wild string

kkran 831 Reputation points

Hi All - Below is my sample output from the query : The Key column is concat of 1_ or 4_ with 'Number' column. My question is if there is same value in the column 'Number' and the key column column starts with both 1 and 4 then i have to select those rows starting with '1_ 'and insert into a another history table.

For example : IDs from 1 to 10, Each row has same 'number' value and these start with either 1 and 4 then i have to move rows 1,3,5,7,9 into the history table starting with only '1_'.

To keep it simple : If the same number value in key column starting with 1 and 4 then only move the 1_ values to history table. Could you please help.
User's image


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,850 questions
{count} votes

1 answer

Sort by: Most helpful
  1. CosmogHong-MSFT 23,556 Reputation points Microsoft Vendor

    Hi @kkran Try this query:

    INSERT INTO History_Table
    SELECT A.* 
    FROM Sample_table A JOIN Sample_table B ON A.NUMBER=B.NUMBER AND A.[KEY]<>B.[KEY]
    WHERE LEFT(A.[KEY],2)='1_'

    Best regards,

    Cosmog Hong

    0 comments No comments