Create delete update tables in one database to another database.

Question

Post reply

Create delete update tables in one database to another database.

sampathmeka

SSC Enthusiast

Points: 192
More actions
April 10, 2020 at 12:02 pm

#3741960

Hi All,
This is sampath i am new to sqlservercentral community,i am not good programmer in sql related queries please helpme to acheive.
Example:-
I have two databases database1 and database2
database1 has 20 tables and database2 has same 20 tables if anything updated/added/deleted any records in database1 tables automatically it should update database2 tables.
Kindly help me if you can
Thanks,
Sampath

Viewing 15 posts - 1 through 15 (of 19 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 1

You likely need a trigger. It's hard to explain how to do this in a post, but look at this

https://www.sqlservercentral.com/articles/auditing-through-triggers

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

Are Database1 and Database2 on the same server instance or are they separate?

The triggers could work if they are on the same server instance, but could be difficult to write and maintain the trigger code to properly handle multiple rows insert or update at the same time. Could you just use synonyms in Database2 to reference the real objects in Database1?

If Database1 and Database2 are not on the same server instance, then you may need some other mechanism, such as scheduled data synchronizations across a linked server or using SSIS, or maybe even replication if changes are needed quickly instead of on a schedule.

sampathmeka SSC Enthusiast Points: 192 More actions · Answer 3

Hi Chris,

currently the database1 and database2 are in same server.just for info i am giving one example

database1 and database2 contains same tables employee,college.

employee table contains fields employeeid ,employeename and employeedesgination.

college table contains fiels studentid ,studentname and studentclass.

Here my requirement is if any employee added/deleted/updated in database1 automatically database2 should be updated.

Here my requirement is if any student added/deleted/updated in database1 automatically database2 should be updated.

Kindly help me with solution,small example is also ok .

Could you just use synonyms in Database2 to reference the real objects in Database1? I am not clear with this question if possible can you explain.

Thanks,

Sampath

Jeffrey Williams SSC Guru Points: 90249 More actions · Answer 4

What is the purpose of having a second database that is exactly the same as the primary database? Why do you need to copy the data to the other database?

A synonym would allow you to have an object in the secondary database that accesses the primary database. For example:

CREATE SYNONYM dbo.MyLocalTable FOR database1.dbo.RemoteTable;

Now - when you create a query in the secondary database you can do this:

SELECT ... FROM dbo.MyLocalTable;

And the data is selected from database1.dbo.RemoteTable

So again - why do you need a copy of the data in the secondary database?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sampathmeka SSC Enthusiast Points: 192 More actions · Answer 5

Hi Williams,

Thanks for making your time for reply!!

why do you need a copy of the data in the secondary database?

My reply:-Actually my task was to update the data across different servers in different locations where the data will be same in all servers.

Any update\Delete\Insert in any table should sync all the servers in different locations.

Initial task is within the server sync two databases. Kindly help me if you have some solution

Thanks,

Sampath

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 6

sampathmeka wrote:

Hi Williams,
Thanks for making your time for reply!!
why do you need a copy of the data in the secondary database?
My reply:-Actually my task was to update the data across different servers in different locations where the data will be same in all servers.
Any update\Delete\Insert in any table should sync all the servers in different locations.
Initial task is within the server sync two databases. Kindly help me if you have some solution
Thanks,
Sampath

There are much better ways to do this rather than rolling your own.

--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)

sampathmeka SSC Enthusiast Points: 192 More actions · Answer 7

Hello Jeff,

Thanks for making your time for reply!!

Can you help me out with better solutions you have that would be great.

Thanks,

Sampath

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 8

I'm not actually the one to ask about such things because the folks I work with found a way to do what they call "San Replication" (the network guys at work spoil the heck out of me). Most folks use one of several different methods such as "Always on" (I believe they just refer to it as "AG"), mirroring, log shipping, replication, etc.

Hopefully, someone else will pick up on this for you.

--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)

Jeffrey Williams SSC Guru Points: 90249 More actions · Answer 9

sampathmeka wrote:

Hi Williams,
Thanks for making your time for reply!!
why do you need a copy of the data in the secondary database?
My reply:-Actually my task was to update the data across different servers in different locations where the data will be same in all servers.
Any update\Delete\Insert in any table should sync all the servers in different locations.
Initial task is within the server sync two databases. Kindly help me if you have some solution
Thanks,
Sampath

Yes - I understand that is the task...but it still does not explain why this is necessary. What issue will this configuration resolve?

This sounds like a solution using MERGE REPLICATION, but I would not recommend that lightly. If you must be able to update a table in ServerA\DatabaseA when that 'same' table is updated in ServerB\DatabaseB - and the reverse...then maybe this is what you are looking for...

I am NOT recommending this...it is a very complex solution that should not be built out through forum posts. If that is truly the goal then you really need to hire a consultant who is extremely versed in merge replication.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sampathmeka SSC Enthusiast Points: 192 More actions · Answer 10

Thanks for the reply Williams!!

Actually i am not sure it is that much complex to write a trigger to delete/update/insert when tables in database1 is changed need to automatically delete/update/insert database2 tables.

Thanks,

Sampath

Jeffrey Williams SSC Guru Points: 90249 More actions · Answer 11

sampathmeka wrote:

Thanks for the reply Williams!!
Actually i am not sure it is that much complex to write a trigger to delete/update/insert when tables in database1 is changed need to automatically delete/update/insert database2 tables.
Thanks,
Sampath

Okay - if you think that isn't a problem I am not sure what concern you have with implementing that solution? However, you stated that updates made in database2 would also have to be replicated to database1 - if that is true then it does become an issue because there can easily be conflicts across databases that you have to reconcile.

But if you are sure a trigger will work...then sure.

It still comes down to the question of Why? What is the purpose of having separate databases that are *exactly* the same? What problem are you trying to resolve by doing this?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sampathmeka SSC Enthusiast Points: 192 More actions · Answer 12

Thanks for the reply Williams!!

I am a beginner in sql related stuff ,i am not not able to achieve my requirement of delete/update/insert when tables in database1 is changed automatically database2 tables need to updated.I need your help in achieving my task.Kindly help me !!

Thanks,

Sampath

Jeffrey Williams SSC Guru Points: 90249 More actions · Answer 13

I cannot help you without understanding the goal of your request. If you can answer why you need to do this and what problem you are trying to resolve - then maybe we can come up with a possible solution.

I doubt it though...as your requirements have changed and you continue asking the same question.

I really don't know what answer you want...but you appear to be looking for a specific answer to your question and I cannot provide whatever answer you are looking for...sorry.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 14

sampathmeka wrote:

...Actually my task was to update the data across different servers in different locations where the data will be same in all servers. Any update\Delete\Insert in any table should sync all the servers in different locations...

Given that we now know the servers are in different locations, that pretty much eliminates the idea of triggers or synonyms to maintain the data.

Is this just a one way transfer of information, from Database1 to Database2, or is there a need to also send updates from Database2 back to Database1?

If this is one direction, then it sounds like Transactional Replication:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver15

If this needs to go both directions, then it sounds like Merge Replication like Jeffery Williams mentioned:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-ver15

Both can be challenging to setup and maintain, but especially so Merge Replication. I think it is still unclear from your responses though the details of what is needed.