Transfer missing table data from one database to another.

Question

Transfer missing table data from one database to another.

barry.nielson

SSC Eights!

Points: 912
More actions
April 29, 2019 at 1:03 am

#3548889
Hi all.
I am in disaster mode right now. I need to know how to copy data from one database (backup) to another (Main).
the data in the backup was recorded the previous day. the main db is missing records, accidentally removed during a duplicate check.
So all I need to do is check what's missing and insert that.
I was thinking something like:
```
Insert into ProductionDB.table from BackupDB.table where ID Not In productionDB.
```
obviously this is not right, so if you know, I would really appreciate.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

aklt SSC Veteran Points: 233 More actions · Answer 1

Your example is "right" its just missing details. So something like

declare @prod table (id integer primary key, col1 varchar(32))
insert into @prod (id, col1) values (1, 'a'), (2, 'b')
declare @backup table (id integer primary key, col1 varchar(32))
insert into @backup (id, col1) values (1, 'a'), (2, 'b'), (3, 'c')

insert into @prod (id, col1)
select id, col1
from @backup
where id not in (select Id from @prod)

select * from @prod
select * from @backup

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Yes, aklt's solution is right. But be careful in case any rows were legitimately deleted after the accidental deletions - you won't want to include those in your INSERT statement.

John

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

In addition to the other posts: Perform the operation in a transaction at first do it with ROLLBACK, so that you can verify that you got the correct results and did not create an even bigger mess. Or perform the operation in a copy of the production database first.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Joe Torre SSChampion Points: 10248 More actions · Answer 4

INSERT INTO P (...)
FROM BACKUPTABLE B
LEFT OUTER JOIN PRODTABLE P
ON B.ID = P.ID
AND P.ID IS NULL;

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 5

Erland Sommarskog wrote:

In addition to the other posts: Perform the operation in a transaction at first do it with ROLLBACK, so that you can verify that you got the correct results and did not create an even bigger mess. Or perform the operation in a copy of the production database first.

Good idea but you should probably check for triggers before doing such a thing with ROLLBACK. Or, do a SELECT that would drive the INSERT without actually doing the INSERT to see what's going to happen.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)