Is there a way to write an insert statement with string_split which ignores duplicates?

I am trying to insert rows into a table using string_split command. It works fine if there are no duplicates. However, if there are duplicates, it errors out. I would like to know how to modify the below so that it skips when it already exists.

I’ve tried modifying the insert statement like below, but it just skips the entire insert statement if it encounters a duplicate.

BEGIN 
     IF NOT EXISTS (select * from table_a
         where column_a = @module
         and column_b in (select value from string_Split(@client_ids, ',')))
    BEGIN
        INSERT IGNORE INTO table_a (column_a, column_b)
        SELECT @module, value FROM string_Split(@client_ids, ',') 
    END
END

Original query…

DECLARE @client_ids VARCHAR(1000);
SET @client_ids = '12345,12346,456789,789459' 
DECLARE @module int = 741852 

-- Insert statement
INSERT INTO table_a (column_a, column_b)
SELECT @module, value FROM string_Split(@client_ids, ',')

To hopefully help clarify a bit… table_a has a bunch of clients in it, many of them have different modules attached to them.

i.e. client 123456 will have modules 789,788,799 and client 123455 will have modules 789,788.

I’ve been given a task to give a list of 50 clients module 799. I only want to update the clients who don’t already have that module associated with them. I can query for a list that already have them and then use excel or something to compare and weed out the list and then just run the query against the ones who don’t have it (which I did yesterday for a quick resolve), but I was hoping that there would be a better way to do it all in one step.

Answer

You can just use SELECT DISTINCT:

INSERT INTO table_a (column_a, column_b)
    SELECT DISTINCT @module, value
    FROM string_Split(@client_ids, ',') ;

EDIT:

Oh, you want to prevent inserts for rows in the table. Use NOT EXISTS as well. If you are just checking for value then:

INSERT INTO table_a (column_a, column_b)
    SELECT DISTINCT @module, value
    FROM string_Split(@client_ids, ',') s
    WHERE NOT EXISTS (SELECT 1
                      FROM table_a a
                      WHERE @module = a.column_a AND
                            s.value = a.column_b
                     );

Advertisement

Answer