Want to update from one Local database table to other server database table continuously after some interval of time.

Question

0.00/5 (No votes)

See more:

Hi, All

I have 3 local database with a table name as "TransactionDetails" with some unique ID for 3 diff machines.
and I have one server database on dekstop, having same table structure and name "TransactionDetails."

I want to copy the contents of all the tables from all machines to one database server table, after certain time of interval, as an when new rows are added to local machine.

How to write sql querry for this and windows service for this? I know windows services, but how to implement overall.

Posted 27-Sep-15 21:28pm

Pdeveloper

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Saksham Agrawal · Accepted Answer · 2015-09-27T21:46:00

Hi,

There are two things that can be done.

1. Update the main table as soon as any row is inserted in any of the tables in local databases.

This can be done using after insert trigger.

2. Update the main table periodically.

This can be done if you create a proc to insert data in the main table from the local table. Create this proc on every local database as your server table can be called from local database.

Schedule this proc on local machines to be executed at whatever interval you want your table to be updated (daily, weekly, monthly).

While inserting you should keep in mind not to insert the data that is already there in the table. For this use:

--Use the code below.
insert into <server name="">.<database name="">.<schema name="">.[Transaction Table]
select * from [Transaction Table] where not exists (select * from <server name="">.<database name="">.<schema name="">.[Transaction Table] )

This will insert only the rows not there in the main table.

Happy Coding !!