Checking for dupes on Compact Insert Statement

Question

I'm not sure if that is the correct term "Compact Insert Statement" It is just what I have always heard it referred to. It goes as follows:

INSERT INTO [tblUsers]
       ([username], [password]) 
VALUES ('user1', 'pass1'), 
       ('user2', 'pass2')

Anyway We have a table of just over 5M rows, and are about to import some data, BUT the probability is high that about 75% of the data is duplicated (we buy data from multiple sources, but they share around 30-40% with each of our sources :/).

If I do a unique constraint on the column, the entire insert fails from that point forward (unless wrapped in a transaction, of course).

I am just at a loss for how to do this efficiently and with code that can be reused going forward.

Answer 1

Why insert into tblusers directly at all?

I always use staging tables. You can use SSIS of course for the same result at with greater complexity

INSERT INTO [staging].[Users]
       ([username], [password]) 
VALUES ('user1', 'pass1'), 
       ('user2', 'pass2')

INSERT INTO [tblUsers]
       ([username], [password]) 
SELECT DISTINCT [username], [password] --edit, added DISTINCT 
FROM [staging].[Users] SU
WHERE NOT EXISTS (SELECT * FROM [tblUsers] U WHERE ...)

Answer 2

By using this type of statement you can't check anything in the same statement. Except if you want to go by transactions, catch unique constraint errors, and in this case add your own code to check/remove duplicates.

A situation where it's possible to do checking directly in the statement would be to use:

Insert into
Select ...
where not exists (...)

But this means to check the data at each insert statement, maybe not a very bright idea.

I'd prefer to add an identity or uniqueidentifier column to use as a primary key, insert anything that comes, and then use a scheduled job to remove the duplicates. That if it's not a problem to have temporary duplicates inside this table.

Or another idea would be to dump anything that comes into a table, without any constraints, and then create a table for live use where you copy only distinct data (do this based on a scheduled job or in a trigger).

Answer 3

This is where SSIS can be handy. You can create a package once and reuse it. If I am assuming correctly: your import errors out when a duplicate id is inserted (collision).

If you were using SSIS, you could redirect your collisions, like say to an error file, or totally ignore that row and go to the next. You can easily look online for tutorials on how the create a SSIS package.

Check this link to read more about the error redirection in SSIS.