How insert data into one database table to other database tb

Atul Patil

8y1.3k

I am working on Azure sql server.

I have two databases DB1 and DB2, DB1 have "rel_user table" and DB2 have "user" table, when i inserting record into "user" table of DB2,

that record automatically inserted into the "rel_user table" of DB1. So, how i achieve these functionality. Thanks in advance.

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
Accepted answer
Whats the use of external table?

A: it can be a table from another DB for another application

Can i insert or update data into external tabel?

A: yes you can just follow what i had mentioned before

please anybody can explain?

A: hope you got your answer.

please don't use triggers, use a SP to do the inserts or what every you want.
8y ago
insert into DB2.user(Id,Name) values(1,'test')

insert into DB1.rel_user(Id,Name) values(select * from DB2.user)
Amit Kumar8y ago
Hi,
Use this code.
INSERT INTO dbo.State(State_Name)
SELECT State_Name
FROM CollegeDb.dbo.State

INSERT INTO dbo.City(State_ID, City_Name)
SELECT State_ID, City_Name
FROM CollegeDb.dbo.City
For mote details you can use the below link

https://www.c-sharpcorner.com/blogs/insert-values-from-one-database-table-to-another-database-table-in-sql-server1
Shahriar Nikkhah8y ago
In AZURE you can not simply use a INSERT INTO from one DB to another
chekc the link that i had provided
Sankara Krishnan Venugopal8y ago
Hi Atul,

The below query will help you to insert data from old database to new one.

Query:

insert into new_database_name.dbo.[table_name] values (Id, Name, details)
select Id,Name,details from old_database_name.dbo[table_name]

Please let me know, if you have any queries. Thanks.
Shahriar Nikkhah8y ago
Atul Please check
https://www.scarydba.com/2016/03/21/cross-database-queries-in-azure-sql-database/
Sandy8y ago
Hi Atul,

this is very simple you can use trigger in this case whenever the record inserts into user table then it will automatically insert the entry in "rel_user_table".

You didn't need any linked server because both the database are on the same server.

if the database is on the different server than there is need of the linked server.

just add the database name before table name in the trigger on any query it should work.
[DbName].dbo.[tableName]
Let me know if you face any issue or this will not resolve your problem.

Thanks
Sandy
Atul Patil8y ago
Both databases on same server( Azure cloud server). So how create Linked server?
Manas Tripathi8y ago
Hi Atul,

Yes, you can insert data in External table. Easy way out is to create a linked server in your SQL server.

Then you can select or insert data using Linked_Server_Name.Schema_Name.Table_Name
Atul Patil8y ago
Whats the use of external table? Can i insert or update data into external tabel? please anybody can explain?
Thanks in advance
Manas Tripathi8y ago
Creating a trigger on rel_user table will serve the purpose.

CREATE TRIGGER trg_rel_user
ON rel_user
AFTER INSERT
AS
IF EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS(SELECT * FROM DELETED)
BEGIN
--- INSERT DATA into user table

INSERT INTO DB2.user (col1, col2)
SELECT col1,col2
FROM INSERTED AS I JOIN INSERTED INS ON I.id= INS.id

END
Shahriar Nikkhah8y ago
its done in 2 steps
1- create a relation between the 2 Databases
https://www.scarydba.com/2016/03/21/cross-database-queries-in-azure-sql-database/
2- create a Stored procedure (SP) in the same db's that does the [insert into] the first table
3- in the same SP with the help of step one, just simple insert into the second DBs

good luck