How to solve below problems

Question

Create 2 new database. And add one table in both database with below columns.
Id,
First name
Last name
Address
Phone no
Email
Date of birth
IsActive

• create trigger and transfer data one database to another database through xml if records is insert and update in both operation.
  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

Answer 1

Good day @Sunil Kumar

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://learn.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://learn.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://learn.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://learn.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.

Answer 2

Hi @Sunil Kumar ,

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,'******@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.