question

Good day @SunilKumar-5549

We are NOT here to solve your homework or replace your employee!

We are here to help people learn to do their job's tasks and to help people that want to learn to become better.

I can guide you to to learn how to solve your homework but you should do the work.

If you stumble a specific issue and you are blocked, then please ask that specific question and provide the information about what you have tried to do and what you did so far, so we will know how to point the answer to the right level and road.

Create 2 new database.

Please check this tutorial on how to CREATE DATABASE

Short version with exercise: https://www.w3schools.com/sql/sql_create_db.asp

Microsoft version with full options: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql

add one table in both database with below columns.

We do not add a table but CREATE TABLE as it is a new object and something exists that you need to add it.

Short version with exercise: https://www.w3schools.com/sql/sql_create_table.asp

Microsoft version with full options: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql

create trigger

CREATE TRIGGER is the next level above the basic SQL queries above. If SQL language is for 10 years old children then I would say that TRIGGERs are for 13 years old children. It is still part of the basic queries but has some more options.

Basically creating a DML trigger is explained here:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql

transfer data one database to another database through xml if records is insert and update in both operation.

Note! This request is not clear to me.

(1) Trigger is not the tool which was build to sync between databases, If you need to sync the data then please inform us and we will point you to the right tool(s)

(2) Why do you need to format the data in XML format in order to pass it between to tabular system?!? The data is already exists in tabular structure so why to CONVERT it twice?

(3) Do you need bidirectional sync? meaning if data is inserted or updated in table A then it should be insert or update to table B ?

parsing the tabular data from the table into XML is done using the hint FOR XML. You can read more about how to do it, in this doc:

https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server

---

Please go over the steps one by one and try to do the work. Provide us the queries which you succeed and explain where (if there is such) you are blocked and what exactly is the step that you need more help.

Hi @SunilKumar-5549,

Welcome to Microsoft Q&A!

Note : Please do not insert data directly into another table. Transfer xml from one database to another database and read data from xml and insert into table in another database

Could you please provide the purpose of above requirement? Did you mean that you would like to generate the inserted or updated data of first database table into xml file, and parse the xml file again , and then insert or update the second database table? It could be better to provide more details or an example.

Right now you could refer to below example without XML transferring.

 create database new1
 create database new2
    
 use new1
 go
    
 drop table if exists Employee
 create table Employee
 (Id int,
 [Firstname] varchar(20),
 [Lastname] varchar(20),
 Address varchar(100),
 [Phoneno] int,
 Email varchar(20),
 [Dateofbirth] date,
 IsActive bit)
    
 use new2
 go
    
 drop table if exists Employee
 create table Employee
 (Id int,
 [Firstname] varchar(20),
 [Lastname] varchar(20),
 Address varchar(100),
 [Phoneno] int,
 Email varchar(20),
 [Dateofbirth] date,
 IsActive bit)
    
 create trigger mytrigger on new1.dbo.Employee
 after insert,update
 as 
 begin
    
 if exists (select 1 from new2.dbo.Employee a inner join inserted b on a.id=b.id)
 begin
    update a 
    set a.[Firstname]=b.[Firstname],a.[Lastname]=b.[Lastname],
    a.Address=b.Address,a.[Phoneno]=b.[Phoneno],
    a.Email=b.Email,a.[Dateofbirth]=b.[Dateofbirth],a.IsActive=b.IsActive
    from new2.dbo.Employee a inner join inserted b on a.id=b.id
 end
 else
 begin
    insert into new2.dbo.Employee
    select * from inserted
 end
 end
    
 insert into new1.dbo.Employee values
 (1,'Ann','Tom','New York 101',123456789,'test123@test.com','2001-09-01',1)
    
 update new1.dbo.Employee
 set [Lastname]='Tommy'
 where id=1
    
 select * from new1.dbo.Employee
 select * from new2.dbo.Employee

Output:

Best regards,
Melissa

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.