How insert data into one database table to other database tb

Question

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.

Shahriar Nikkhah · 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.

Manas Tripathi · Answer

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

Shahriar Nikkhah · Answer

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

Shahriar Nikkhah · Answer

In AZURE you can not simply use a INSERT INTO from one DB to another chekc the link that i had provided

Sankara Krishnan Venugopal · Answer

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.

Sandy · Answer

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 Patil · Answer

Whats the use of external table? Can i insert or update data into external tabel? please anybody can explain? Thanks in advance

Manas Tripathi · Answer

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

Chirag Rathod · Answer

insert into DB2. user (Id, Name ) values (1, 'test' ) insert into DB1.rel_user(Id, Name ) values ( select * from DB2. user )

Shahriar Nikkhah · Answer

Atul Please check https://www.scarydba.com/2016/03/21/cross-database-queries-in-azure-sql-database/

Atul Patil · Answer

Both databases on same server( Azure cloud server). So how create Linked server?

Amit Kumar · Answer

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

How insert data into one database table to other database tb

Answers (12)