insert error check

Question

insert error check

TRACEY-320982

SSChampion

Points: 13653
More actions
August 4, 2010 at 8:59 pm

#225126

The data does not exist in main table
INSERT INTO MAIN_TABLE
SELECTCOLUMN1, COLUMN2
FROM TEMP_TABLE
WHERE NOT EXISTS
(SELECT * FROM MAIN_TABLE) W
WHERE
W.COLUMN1 = COLUMN1 AND
W.COLUMN2 = COLUMN2)
So i am doing a where not exist to make sure before inserting the records. I am finding that the records are not being inserted.
If i remove the where not exist this works.
How to make sure record does not exist before inserting it.
The select statement may consist of a million rows.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

I'm assuming their is a unique index or a constraint on MAIN_TABLE. If you have duplicate values for those two columns you have a problem. The entire set is inserted as one operation and either succeeds or fails as a whole.

SELECT COLUMN1, COLUMN2, COUNT(*)

FROM TEMP_TABLE

GROUP BY COLUMN1, COLUMN2

HAVING COUNT(*) > 1

The code above will tell you if that's your problem. If any combinations show up with a count greater than one, you are trying to insert two rows with the same key. You could correct this by deleting the duplicate rows or by doing a SELECT DISTINCT COLUMN1,COLUMN2.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

WayneS SSC Guru Points: 95460 More actions · Answer 2

TRACEY-320982 (8/4/2010)
The data does not exist in main table
INSERT INTO MAIN_TABLE
SELECTCOLUMN1, COLUMN2
FROM TEMP_TABLE
WHERE NOT EXISTS
(SELECT * FROM MAIN_TABLE) W
WHERE
W.COLUMN1 = COLUMN1 AND
W.COLUMN2 = COLUMN2)
So i am doing a where not exist to make sure before inserting the records. I am finding that the records are not being inserted.
If i remove the where not exist this works.
How to make sure record does not exist before inserting it.
The select statement may consist of a million rows.
Thanks

How about:

INSERT INTO MAIN_TABLE

SELECT COLUMN1, COLUMN2

FROM TEMP_TABLE

EXCEPT

SELECT COLUMN1, COLUMN2

FROM MAIN_TABLE

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 3

TRACEY-320982

SSChampion

Points: 13653

August 5, 2010 at 7:42 pm

#1203218

I will take a look at the EXCEPT condition.

Thanks