CODEWITHSUNDEEP
sqlsql-server-2008sql-insert
Tim Vavra
Tim Vavra

Reputation: 537

Insert missing records

I have a sql server 2008 r2 table that contains thousands of records which have been updated by a separate program. Due to an oversight there are a series of missing records. I need to insert the missing records into the database. If the records exists (ANumber and Stage match the table of possible missing records) then go to the next record, if it doesn't exist insert the record (ANumber, Stage, field3 through field10.)

What I was thinking of doing was first create the missing records table (fields 1 -10). Name the table tbMissRec. My existing table is MstrAnno.

This is where I am not sure about the syntax. I have seen merge commands but what I think of is if then kind of logic

If (tbMissRec.ANumber = MstrAnno.ANumber) and (tbMissRec.Stage =
MstrAnno.Stage) then Else insert into MstrAnno
tbMissRec.ANumber,tbMissRec.Stage, etc)

I am sure that this is not the right syntax, but I think that the logic is evident.

I just need to insert records if they don't exist.

Upvotes: 1

Views: 190

Answers (2)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

I prefer this format as it's easy to comment out the insert and verify the data that would be inserted. Also, you should specify column names in your inserts and selects.

INSERT INTO MstrAnno (ANumber,Stage)
    SELECT A.ANumber,A.Stage
    FROM tbMissRec A
    LEFT JOIN MstrAnno B ON B.ANumber = A.ANumber AND B.Stage = A.Stage
    WHERE B.ANumber IS NULL

Upvotes: 1

Lamak
Lamak

Reputation: 70638

This should do:

INSERT INTO MstrAnno
SELECT *
FROM tbMissRec A
WHERE NOT EXISTS(SELECT 1 FROM MstrAnno
                 WHERE ANumber = A.ANumber 
                 AND Stage = A.Stage)

Upvotes: 2

Related Questions