-3

I have a database of more than 500 tables and now there is a copy of that database from an old backup.We noticed that there are many missing records in the new database which I would like to update from the old?

Whats the best practice to update all the records from another database, We can do it individually using queries but it's time-consuming and we have a deadline. Is there any alternative or best way to accomplish this.

Regards

aryan
  • 49
  • 6
  • this is too broad a question. how are you going to choose which records are correct if there are conflicts with ids? are you going to perform updates if some records have changed? are you going to delete records if required? what if records with relationships that rely on primary keys need to be added but id's are taken by other records. sounds like a issue that is not going to be resolved by your deadline. – Tanner Oct 03 '17 at 20:28
  • There are many third party tools that can do data comparison across databases. SQL Data Compare from redgate is one of them. Something like that is about as close as you can get to an easy button for this type of thing. It is incredibly complicated for things like handling when the schema is different. I love deadlines. I like the whooshing sound they make as they fly by. – Sean Lange Oct 03 '17 at 20:29

1 Answers1

1

First thing to point out is this could be inherently dangerous.

You would want to have timestamps on each table for performance, otherwise you are going to run into potential record collision problems. Without timestamps you REALLY want to be certain you have no identity_inserts on and that the database backup has not been written to independently of the current system.

But if you have timestamps, or a guaranteed way if identifying records to avoid collision etc, and you REALLY want to roll your own rather than use one of the data comparison tools available then...

You could write a stored procedure with a dynamic query and iterate over each table with INFORMATION_SCHEMA.TABLES and perform an insert statement created.

EXEC sp_execute '' is the structure for dynamic queries. Create a variable for the query, use a cursor to iterate over the INFORMATION_SCHEMA_TABLES and INFORMATION_SCHEMA_TABLES to construct SQL statements.

Hopefully that is enough information to help you construct a dynamic SQL query that would effectively do something like the following on each table:

INSERT INTO TableA(Col1, Col2, ColN) 
SELECT
    Col1, Col2, ColN 
FROM
    OldDatabase.TableA
WHERE
    TableA.[ID or Timestamp] NOT IN OldDatabase.[ID or TIMESTAMP]

But you really want to examine WHY you are missing those records. Perhaps also insert them into a logging table to ensure you can go back afterwards and examine why.

Hope this helps.

Alan
  • 1,378
  • 2
  • 19
  • 24