SQL Server: Insert if doesn't exist, else update and insert in another table

Question

I'm inserting new records into a Person table, and if there's already a record with the same SSN, I want to backup this old record to another table (let's call it PersonsBackup) and update the row with my new values. There is an identity column in Person table that serves as my primary key, which has to be the same.

Source table structure:

Name | Addr | SSN

Person table structure:

PrimaryKeyID | Name | Addr | SSN

PersonBackup table structure:

BackupKeyID | Name | Addr | SSN | OriginalPrimaryKeyID

where OriginalPrimaryKeyID = PrimaryKeyID for the record that was backed up. How can this be done? I was thinking of using cursor to check if SSN matches, then insert that record accordingly, but I've been told that using cursors like this is very inefficient. Thanks for your help!

Have a look at the MERGE statement, it can do the job: msdn.microsoft.com/en-us/library/bb510625.aspx — NickyvV
– NickyvV, Commented Aug 5, 2014 at 15:03
@NickyvV From what I know, MERGE doesn't support two target tables, does it? — user3808188
– user3808188, Commented Aug 5, 2014 at 15:18

Dave.Gugg · Accepted Answer · 2014-08-05 15:39:18Z

4

You can do so like this, combine the insert/update using MERGE

INSERT INTO PersonBackup
SELECT P.Name, P.Addr, P.SSN, P.PrimaryKeyID
FROM Person P
INNER JOIN source s ON P.SSD = s.SSD

MERGE Person AS target
USING (SELECT Name, Addr, SSN FROM SOURCE) AS source (NAME, Addr, SSN)
ON (target.SSN = source.SSN)
WHEN MATCHED THEN   
    UPDATE SET name = source.name, Addr = source.Addr
WHEN NOT MATCHED THEN
    INSERT(Name, Addr, SSN)
    VALUES(source.name, source.addr, source.SSN)

edited Aug 5, 2014 at 15:39

answered Aug 5, 2014 at 15:21

Dave.Gugg

6,8013 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3808188 Over a year ago

Isn't this inserting ALL records from Person into PersonBackup? I'm only backing up the values that are replaced..

Dave.Gugg Over a year ago

Edited the first select so it only grabs records in Person AND Source.

user3808188 Over a year ago

I like this answer for its simplicity and brevity. Thanks!

Kyle Hale · Accepted Answer · 2014-08-05 15:01:53Z

0

Here is some pseudocode to get you started:

Insert into PersonBackup table all Person data where SSN joins to Source data
Insert into Person table all Source data where SSN doesn't join to Person data
Update Person table all Source data where SSN joins to Person Data

So some hints:

Figure out a query that returns Person data where SSN is in the Source data.
Figure out a query that returns Source Data where SSN isn't in Person data.
Check out the syntax for INSERT INTO.
Check out the syntax for UPDATE FROM.

answered Aug 5, 2014 at 15:01

community wiki

Kyle Hale

Comments

i-one · Accepted Answer · 2014-08-05 15:49:36Z

Assuming that BackupKeyID is identity in the PersonBackup table, you may try update statement with the output clause followed by insert of the records not existing in the target table:

update p
set p.Name = s.Name, p.Addr = s.Addr
output deleted.Name, deleted.Addr,
    deleted.SSN, deleted.PrimaryKeyID into PersonBackup
from Source s
    join Person p on p.SSN = s.SSN;

insert into Person (Name, Addr, SSN)
select s.Name, s.Addr, s.SSN
from Source s
where not exists (select 1 from Person where SSN = s.SSN);

or using insert into ... from (merge ... output) construct in a single statement:

insert into PersonBackup
select Name, Addr, SSN, PrimaryKeyID
from
(
    merge Person p
    using (select Name, Addr, SSN from Source) s
    on p.SSN = s.SSN
    when matched then
        update set p.Name = s.Name, p.Addr = s.Addr
    when not matched then
        insert (Name, Addr, SSN) values (s.Name, s.Addr, s.SSN)
    output $action, deleted.Name, deleted.Addr, deleted.SSN, deleted.PrimaryKeyID)
as U(Action, Name, Addr, SSN, PrimaryKeyID)
where U.Action = 'UPDATE';

Imran · Accepted Answer · 2014-08-06 08:16:11Z

0

DECLARE @TEMP TABLE (SSN VARCHAR(100),Name VARCHAR(MAX),Addr VARCHAR(MAX) ), 
 @SSN VARCHAR(100), 
 @Name VARCHAR(MAX),
 @ADDR VARCHAR(MAX)

//INSERT YOUR VALUES INTO THIS TEMP VARIABLE FIRST. 


SET @SSN = (SELECT SSN FROM @TEMP)
SET @Name = (SELECT NAME FROM @TEMP)
SET @Addr = (SELECT ADDR FROM @TEMP)


IF EXISTS (SELECT  1 FROM Person_table WHERE SSN = @SSN)
BEGIN

    //BACKUP FIRST
    INSERT INTO PersonBackup_table
    SELECT * FROM Person_table WHERE SSN = @SSN

    //UPDATE NEXT
    UPDATE A
    SET A.NAME = @NAME,
        A.ADDR = @ADDR
    FROM Person_table A
    WHERE A.SSN = @SSN


END
ELSE
BEGIN

    INSERT INTO Person_table VALUES @Name,@Addr,@SSN
END

answered Aug 6, 2014 at 8:16

Imran

13 bronze badges

Collectives™ on Stack Overflow

SQL Server: Insert if doesn't exist, else update and insert in another table

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related