Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

Move Data - Different Schemas

I have 2 SQL 2008 databases.  In database "A" I have a table that I want to extract data from and then import that data into a table within database "B".  The schema is a bit different and has different column names so it's not just a straight copy.  I need to have the data synced up only when it changes.  

Any thoughts on how I would do this?  I know I can use SSIS to move data but I'm wondering how to move the data and set it to run when the data changes.

Should I just create a job?  look at replication?
  • 2
1 Solution
Raja Jegan RSQL Server DBA & ArchitectCommented:
Kindly confirm whether both databases resides in a single server or not..
If so, then you can create a trigger to copy the records from Database A to Database B on specific events based with a Custom INSERT statement( since your columns are not matching)
Westside2004Author Commented:

Same server, different database.

If both databases resides in a single server, then I suggest go by rrjegan17. If not,
you should setup distributed computing using four part name i.e. [server].[database].[schema/owner].[table] and then instead of remote table name in INSERT in trigger use this distributed name.
Raja Jegan RSQL Server DBA & ArchitectCommented:
Since both databases are in same server, trigger would work faster compared to the other options..
And let us know if you need any help in constructing triggers for this scenario..

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now