samedi 25 avril 2015

Transform and copy table data


Sorry if this questioned was asked before, but i am posting it, since was not able to find anything related in forum. So any help would be appreciated. Here is my scenario.

We have an SQL table view A (just read access) with set of data located on Server A on domain A. We have our SQL database with table B located on Server B on Domain B. We have got the port opened up between 2 domains.

We need to write a SSIS job (runs per day) which will get the data from View A and insert in Table B. So what could be the best way for achieving this.

Note:

  • table View on server doesn't have deleted or last modified columns So wont be able to figure out what are the changes. (if there is any other way please do let me know)
  • Database data is around 50k records

Approach 1:

  • Export data from View
  • Transform it
  • Drop table on Server B
  • Insert the transformed data

Approach 2:

  • Export data from view
  • Transform it
  • Insert into a temp table
  • Make a log entry into log table
  • When the log entry says successfully copied, trigger to delete all the rows and copy the data from temp table to Table B.
  • If something went wrong, rollback.

Aucun commentaire:

Enregistrer un commentaire