locked
copying table from one server to another RRS feed

  • Question

  • Is there any way I can schedule job for copying one table from prod server to another every night? 

    Thanks

    Wednesday, October 1, 2014 4:47 PM

Answers

  • OK, what I understand is that you have "active" server and another "passive". any data that insert the active server should be copy to the other server. Is that correct?

    1. If so, then you should look for "replication" you can create replication from the active server to the passive server and copy all or one or several tables. check those links (this is from Google and I did not check them all together, just very fast look)

    http://www.codeproject.com/Articles/715550/SQL-Server-Replication-Step-by-Step

    http://msdn.microsoft.com/en-us/library/aa337389.aspx

    2. You can create trigger on update and on insert, on the table that you want to copy the data from. Check those links:

    http://msdn.microsoft.com/en-us/library/ms189799.aspx

    http://www.codeproject.com/Articles/38808/Overview-of-SQL-Server-database-Triggers

    3. You can use simple MERGE query to update and insert data from the active server to the passive server. In order to work with 2 databases in the same query you need to use four parts name(check this link http://www.mssqltips.com/sqlservertip/1095/sql-server-four-part-naming/)

    and to learn about MERGE check those links:

    http://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

    http://www.purplefrogsystems.com/blog/2011/12/introduction-to-t-sql-merge-basics/


    [Personal Site]  [Blog]  [Facebook]
    signature

    • Proposed as answer by Visakh16MVP Thursday, October 2, 2014 5:09 AM
    • Marked as answer by Charlie Liao Sunday, October 12, 2014 8:21 AM
    Thursday, October 2, 2014 4:56 AM
  • 'There is always a way.'  I love it.

    In addition to the great suggestions you received, you may want to consider this.

    http://www.w3schools.com/sql/sql_insert_into_select.asp

    http://www.codeproject.com/Tips/664327/Copy-Table-Schema-and-Data-From-One-Database-to-An

    You can also right-click the table and select 'Script Table As'.  Then follow the prompts.

    I'm guessing you don't have the Developer Edition of SQL Server.  So, see this for creating a batch file to run the SProc.

    http://stackoverflow.com/questions/12980353/execute-stored-procedure-from-batch-file

    If you do have SQL Server Developer, it's even easier to schedule your job!

    https://www.youtube.com/watch?v=De_GOwnTDjo


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Charlie Liao Sunday, October 12, 2014 8:21 AM
    Wednesday, October 8, 2014 7:18 PM

All replies

  • Hi

    First there is always a way :-) In this case like most cases there are several options (more the 10 differents options)

    Are you sure that you want copy the table each time, if so, you need to drop the old table. dont you want just to copy the data? maybe just the new data?

    try to clarify please :-)

    Basically, you can use replication, simple openrowset query, SSIS project and more. is one of those option sounds familiar? If so than we can focus on this option.


    [Personal Site]  [Blog]  [Facebook]
    signature

    Wednesday, October 1, 2014 4:54 PM
  • Hello Pituach Ariely,

    Basically I want copy data every time or Copy new table and delete the old one.. Is there any script or SSIS package create link ?? Thanks for your help

    Wednesday, October 1, 2014 4:57 PM
  • OK, what I understand is that you have "active" server and another "passive". any data that insert the active server should be copy to the other server. Is that correct?

    1. If so, then you should look for "replication" you can create replication from the active server to the passive server and copy all or one or several tables. check those links (this is from Google and I did not check them all together, just very fast look)

    http://www.codeproject.com/Articles/715550/SQL-Server-Replication-Step-by-Step

    http://msdn.microsoft.com/en-us/library/aa337389.aspx

    2. You can create trigger on update and on insert, on the table that you want to copy the data from. Check those links:

    http://msdn.microsoft.com/en-us/library/ms189799.aspx

    http://www.codeproject.com/Articles/38808/Overview-of-SQL-Server-database-Triggers

    3. You can use simple MERGE query to update and insert data from the active server to the passive server. In order to work with 2 databases in the same query you need to use four parts name(check this link http://www.mssqltips.com/sqlservertip/1095/sql-server-four-part-naming/)

    and to learn about MERGE check those links:

    http://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

    http://www.purplefrogsystems.com/blog/2011/12/introduction-to-t-sql-merge-basics/


    [Personal Site]  [Blog]  [Facebook]
    signature

    • Proposed as answer by Visakh16MVP Thursday, October 2, 2014 5:09 AM
    • Marked as answer by Charlie Liao Sunday, October 12, 2014 8:21 AM
    Thursday, October 2, 2014 4:56 AM
  • 'There is always a way.'  I love it.

    In addition to the great suggestions you received, you may want to consider this.

    http://www.w3schools.com/sql/sql_insert_into_select.asp

    http://www.codeproject.com/Tips/664327/Copy-Table-Schema-and-Data-From-One-Database-to-An

    You can also right-click the table and select 'Script Table As'.  Then follow the prompts.

    I'm guessing you don't have the Developer Edition of SQL Server.  So, see this for creating a batch file to run the SProc.

    http://stackoverflow.com/questions/12980353/execute-stored-procedure-from-batch-file

    If you do have SQL Server Developer, it's even easier to schedule your job!

    https://www.youtube.com/watch?v=De_GOwnTDjo


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Charlie Liao Sunday, October 12, 2014 8:21 AM
    Wednesday, October 8, 2014 7:18 PM