Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 7 down vote accepted

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
share|improve this answer
+1 Yes staging tables are very handy – bernd_k Jun 7 '11 at 17:05
where not exists or would it be better to left join on null? – jcolebrand Jun 7 '11 at 18:21
@jcolebrand: Personal choice. I prefer NOT EXISTS and EXISTS because they are always correct (NOT IN fails on NULL, JOINs may requires DISTINCT) and I try to be consistent. For this kind of query you don't need data from the OUTER table) LEFT JOIN..IS NULL is normally a lot slower because the JOIN happens before the WHERE – gbn Jun 7 '11 at 18:35
@gbn +1 on use of staging, though I would add a distinct, as @Jeremy said there'd be a high change of duplication (including in the staging table) – Andrew Bickerton Jun 8 '11 at 15:00
@gbn: I don't think it's true that "LEFT JOIN..IS NULL is normally a lot slower because the JOIN happens before the WHERE". I'm sure the optimizer knows to take the complete statement and build it in a favorable way taking into account all steps of the Select. – Marian Jun 9 '11 at 10:50

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).

share|improve this answer

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.