CODEWITHSUNDEEP
sqlsql-server
Bhavana Kaparthy
Bhavana Kaparthy

Reputation: 15

How to insert records into one server database when records inserted in the another SQL Server database

I am working on online and store ordering Pizza project. In this project I have two different databases one is the server where online ordered records will insert and another one is another server database which runs only in the stores.

My requirement is whenever any order is placed in the server database, then that same order must be inserted into the another server database automatically without using triggers.

Is there any solution please let me know ?

Bhavana Ky

Upvotes: 1

Views: 89

Answers (3)

Alec.
Alec.

Reputation: 5525

None of the other answers seem to have mentioned this enough but a key part of what you need is a trigger.

A trigger allows you to update/insert multiple tables when a condition is met on your original table, in this case the condition would be inserted

Couple this will a linked server and it should be fairly stright forward to achieve what you need.

Triggers

Linked Servers

Upvotes: 0

Dan
Dan

Reputation: 10680

As an alternative to using Linked Servers as suggested elsewhere, there are various other options depending on your replication requirements:

  • Log shipping Allows you to periodically send transaction logs from the primary server to one or more secondary servers. More info
  • Database mirroring Typically used to increase availability of SQL Server databases, automatic failover, etc. More info
  • Replication, Change Tracking, Change Data Capture are features within AlwaysOn availability groups (only SQL Server 2012 or above), to easily automate replication. More info

These alternatives help you replicate data at the database level rather than at the application level (you will need custom code, triggers, etc. when replicating data using Linked Servers).

Upvotes: 2

Adrian Nasui
Adrian Nasui

Reputation: 1095

I have done this in the past with good results:

Use the SQL Linked Server functionality to link the servers. (How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005)

Then make some sort of a job that copies the new rows from the source do the destination tables.

You will need some sort of incremental field (an bigint is usually enough) to know what your last synced row was when you run the job again.

Upvotes: 0

Related Questions