How to create windows service that on database table data insert into another database table.

Question

0.00/5 (No votes)

See more:

I have 2 Databases
1: ADB
2: BDB

ADB contains ADBTabe with 3/more fields
BDB contains BDBTabe with 3 fields fields

When the records inserted into "ADB.ADBTabe" then the job should be take data from that ADB table and insert into BDB.BDBTable

So could you please provide the approach that how to manage all these things.

What i have to implement sql server DB side?
How can i implement Windows / Any other job for this?

Could you please give me the proper suggestion.

What I have tried:

I have no idea about the scenario i am new

Posted 28-Feb-16 23:13pm

DGKumar

Updated 17-Jul-18 22:50pm

Manas_Kumar

v2

Add a Solution

Comments

dan!sh 29-Feb-16 5:21am

Why not just run 2 insert queries?

[no name] 29-Feb-16 6:07am

Do you want to run the service/job interval basis? Means it will run in every 5/10/.. minutes.

If answer is "no" then you don't need any job/windows to implement.

Sinisa Hajnal 29-Feb-16 6:34am

Why not create linked servers and insert either directly from stored procedure (run two inserts in a transaction) or with OnInsert trigger?

DGKumar 1-Mar-16 4:26am

Yes i want to create service or job to insert the data into another db table by using linked server by using stored procedure. Could you please provide the better approach or links to do it.

4 solutions

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)

iliyas.patel · Answer 1 · 2016-02-29T03:11:00

Solution 1

Write a webservice(can be WCF service) which will fetch data from BDB and insert data into BD.(Straight forward)

Now as soon as you update ADB.ADBTabe then trigger a notification to webservice. It will will fetch data from BDB and insert into BD.

Let me know if I you have any doubt.

Posted 29-Feb-16 3:11am

iliyas.patel

Comments

DGKumar 29-Feb-16 23:28pm

Thank you for your response.
I think every time the complete data will fetch and insert into destination source. Right?
Assume the source db table contain above 10000 records first time after that i have inserted one record in source then what will happen in destination db table, how can i identify and insert that particular newly inserted record only in destination db table.

iliyas.patel · Answer 2 · 2016-02-29T19:49:00

Solution 2

Solution 1:
Create a column (say "updatedrow") by default which will be true. If you insert/update new row then set updatedrow to false for that record.

Solution 2:
Create a new table "xyz" which will hold primary key of newly updated row. later move the data by using primary keys present in the table xyz.

Solution 3: Run multiple queries to update both table, but it may degrade application performance.

Posted 29-Feb-16 19:49pm

iliyas.patel

Updated 29-Feb-16 19:52pm

v2

Comments

CHill60 3-Mar-16 7:06am

Which one of your two solutions is supposed to be the answer?
Please don't post multiple solutions to the same question - it is very confusing. Instead use the Improve solution link to update your original solution.

RickZeeland · Answer 3 · 2016-03-04T10:12:00

Solution 3

May I suggest using SQL Dependency, see the article:

SQL Dependency with C#.NET and SQL Server 2012[^]

If you have a newer SQL Server version you can also use "Change tracking", see:

SQL Server Change Tracking on Table (Without Triggers)[^]

Posted 4-Mar-16 10:12am

RickZeeland

ajay kotnala · Answer 4 · 2018-07-17T22:50:00

Solution 4

c# - SqlDependency code listening to inserts/update in a table from windows service - Stack Overflow[^]

Posted 17-Jul-18 22:50pm

ajay kotnala