CODEWITHSUNDEEP
sqlsql-servert-sqlinsert
10e5x
10e5x

Reputation: 909

How to only insert non-duplicate rows in SQL Server 2005?

I have tried the following. It does not insert any new rows, although no rows are duplicate either. Why is that so? What do I do wrong? any help are appreciated 

     INSERT INTO TEMP_8
     SELECT PASS_M, ENTRY_DT, EXIT_D, WORKED_HRS, 
            CONSECUTIVE_D = num -(SELECT max(num)
                                  FROM TEMP_7 B
                                  WHERE B.CONSEC <= A.CONSEC
                                        AND B.GAP = 1
                                        AND A.IC_N = B.IC_N) + 1
     FROM TEMP_7 A
     WHERE NOT EXISTS (SELECT *
                       FROM TEMP_8);

Update: 

PASS_M ENTRY_DT, EXIT_DT, WORKED_HRS  
peter 20/12/12 21/12/12 12  
Tom 20/12/12 21/12/12 10  
Tom 21/12/12 22/12/12 9

These are not duplicates. A duplicate is when the name, entry_dt and exit_dt is exactly the same.

Upvotes: 0

Views: 1483

Answers (2)

Atheer Mostafa
Atheer Mostafa

Reputation: 735

You need to filter the Exists with the data in select as follows:

 INSERT INTO TEMP_8
 SELECT PASS_M, ENTRY_DT, EXIT_DT, WORKED_HRS, 
        CONSECUTIVE_D = num -(SELECT max(num)
                              FROM TEMP_7 B
                              WHERE B.CONSEC <= A.CONSEC
                                    AND B.GAP = 1
                                    AND A.IC_N = B.IC_N) + 1
 FROM TEMP_7 A
 WHERE NOT EXISTS (SELECT 1
                   FROM TEMP_8 T
 WHERE A.PASS_M=T.PASS_M AND A.ENTRY_DT=T.ENTRY_DT AND A.EXIT_DT=T.EXIT_DT);

Upvotes: 1

Alexey A.
Alexey A.

Reputation: 902

 INSERT INTO TEMP_8
 SELECT PASS_M, ENTRY_DT, EXIT_D, WORKED_HRS, 
        CONSECUTIVE_D = num -(SELECT max(num)
                              FROM TEMP_7 B
                              WHERE B.CONSEC <= A.CONSEC
                                    AND B.GAP = 1
                                    AND A.IC_N = B.IC_N) + 1
 FROM TEMP_7 A
 WHERE NOT EXISTS (SELECT *
                   FROM TEMP_8 B
                   WHERE B.PASS_M = A.PASS_M 
                   and B.ENTRY_DT = A.ENTRY_DT 
                   and B.EXIT_D = A.EXIT_D);

Upvotes: 2

Related Questions