Hi all, I have two different servers which are sql server 2005 and another one is SQL Server 2008. Now I need to copy data from selected table from SQL Server 2008 to SQL Server 2005. Whatever changes made in sql server 2008 should be effected in sql server 2005. Currently by using linked server connecting to 2008 server, selecting data and inserting into 2005 server. Before insertion Im deleting all the data from the table in 2005 server. This process is taking a long period to execute. Can anybody suggest me any other way for this problem? Thanks.thanx

How do you delete all the data? Do you use a TRUNCATE command? If the set of changed data is relatively small, you can use an incremental load method: http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspxMCTS, MCITP - Please mark posts as answered where appropriate.

Yes, Im using truncate command to do that. i tried the link which u gave me. it's working fine for new data and update data not for delete. <o:p></o:p> Data which i need to copy also very huge. Any idea??<o:p></o:p> thanx

You can transfer the data via OLEDB provide (SSIS) with Fast load optionBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Yes, Im using truncate command to do that. i tried the link which u gave me. it's working fine for new data and update data not for delete. <o:p></o:p> Data which i need to copy also very huge. Any idea??<o:p></o:p> What is huge? Can you elaborate more on your scenario? Are the source and destination databases the same in structure, or do you need to do additional transformations?MCTS, MCITP - Please mark posts as answered where appropriate.

The source and destination database structure is same. I need to transfer data from 8 tables. Per table will have more than 1000 records per day. After few weeks it will be reach to 10k data. Thats where Im confused now. thanx

1000 rows is nothing for SSIS. Truncate the tables in SSIS with an Execute SQL Task. Transfer all rows from source to destination and use the Fast Load option in the OLE DB Destination as Uri mentioned. This shouldn't take more than 1 minute in SSIS.MCTS, MCITP - Please mark posts as answered where appropriate.

1000 rows is nothing for SSIS. Truncate the tables in SSIS with an Execute SQL Task. Transfer all rows from source to destination and use the Fast Load option in the OLE DB Destination as Uri mentioned. This shouldn't take more than 1 minute in SSIS.MCTS, MCITP - Please mark posts as answered where appropriate.