SQL Server - COPYING DATA FROM ANOTHER DATABASE AT SIMULTANEOUSLY

Asked By kumar on 14-Feb-12 06:00 AM
HOW TO INSERT THE RECORD IN ONE DATABASE TABLE AND IT SIMULTANEOUSLY INSERT INTO ANOTHER DATABASE TABLE.

for example,

StudentDetails table in 
STUDENT database and StudentInfo in Employee database and i want to insert record on StudentDetails same time the record should be inserted into studentInfo in Employee database  
Reena Jain replied to kumar on 14-Feb-12 06:02 AM
hi,

use the following query to fill the data from one table to another table

  • if want to copy some fields only or fields are different of both table
Insert into Database2.Table2 (field1, field2) select field1, field2
from Database2.Table1

  • if want to copy all fields  or fields are same of both table
Insert into Database2.Table2  select * from Database2.Table1

Hope this will help you
Venkat K replied to kumar on 14-Feb-12 06:19 AM
Inserting records to multiples database at same time is practically not possible, you need to use Transactions to maintain synchronization between two databases

BEGIN
TRY
BEGIN TRAN T1
USE Student
INSERT INTO StudentInfo SELECT * from tblname

USE Employee
INSERT INTO StudentDetails SELECT * from tblname

COMMIT TRAN T1
CATCH

ROLLBACK TRAN T1
END TRY

hope this helps!
dipa ahuja replied to kumar on 14-Feb-12 06:40 AM
You can write trigger for it

ALTER TRIGGER Trigger1 ON dbo.table1
FOR Insert
AS

BEGIN
   /* code to write update/insert in table2 */
END