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

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)

Isn't this inserting ALL records from Person into PersonBackup? I'm only backing up the values that are replaced..
Edited the first select so it only grabs records in Person AND Source.

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

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.

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

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

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

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Related