CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
S.A.R
S.A.R

Reputation: 49

Update with Data from One to another Database

  1. I have two databases: db1 is server, db2 is local.
  2. db2 I updated from the db1 database.
  3. And added 4000 new records to db2.
  4. And I want to update db1 (server) from db2 (local), but some new records have been added to db1 (server).
  5. Now when I want to do Restore Database, some new records from db1 (server) will disappear. How to make it so that when I update db1 (server) the added some records are not lost and new 4000 data added from db2 (local)? Can anyone tell me a way or a tool to compare the data?

Upvotes: 1

Views: 566

Answers (1)

gotqn
gotqn

Reputation: 43626

You need to be able to read/load the data from the local database to your server database. This can be done via Linked Servers - if the servers are linked you can query data from the db2 from the context of db1.

It will looks like:

SELECT *
FROM [server].[db2].[schema].[table];

If this is not possible I am guessing you have another way to move the data there (via BCP command or maybe just script the table and the data using SSMS from db2 and create it in db1 (4000 rows is not much and you should be OK to just copy the generated SQL).

Now, having the data in one place, you must have a way to identify which rows to insert.

This can be done easily like this:

SELECT *
FROM db2Table
EXCEPT
SELECT *
FROM db1Table

So, if there is row in the db2Table that is not found in the db1Table it will be returned. Then, just insert these rows.

Please, note that EXCEPT with * is comparing all columns. So, if you have a column like ID INT IDENTITY(1,1) you will need to exclude it from the SELECT list.

Also, if you have a way to map the data between the two tables by ID or GUID (a lot of folks are using UNIQUEIDENTIFIER to synch data), you make perform more complex operations. For example:

  1. If the GUID record is missing in the db1 table - insert it
  2. if the GUID record is found in the db1 table - update it

Upvotes: 1

Related Questions