| Author |
Topic |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
 Posted - 2008-03-20 : 16:57:46
|
| I have a table which has no primary key, so if I want to copy the data of the table from sql 2000 server to SQL2005 server which of the following options can I use:1.Snapshot replication2.DTS package: If DTS, how do I identify the new rows so that I should be able to copy only the new/updated data into the sql2005 server?Thanks! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-20 : 17:06:43
|
| You can use:1) Log shipping with standby option2) Merge Replication( But add Guid column in table)3) Schedule the DTS packages ( Use Date function to select only recent records). |
 |
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-03-20 : 17:27:14
|
quote:
Originally posted by sodeep
You can use:1) Log shipping with standby option2) Merge Replication( But add Guid column in table)3) Schedule the DTS packages ( Use Date function to select only recent records).
Sorry I forgot to mention that the database in destination SQL2005 server will be used for running reports, so in that case can we use log shipping?? In this situation which one will be good?? Thanks!! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-20 : 17:35:15
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99207 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-03-20 : 18:10:26
|
| You cannot log ship from a SQL 2000 server to a SQL 2005 server.CODO ERGO SUM |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-20 : 18:32:02
|
| OH! my bad . I thought 2000- 2000. Good catch Mike. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-20 : 23:43:08
|
| DO you need real time data sync? How big the table is? |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-03-21 : 12:43:16
|
| If the data sync happens every night that's fine. About 700k rows, but the table keeps incrasing on a daily basis. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-21 : 12:53:00
|
| Schedule the package to run every day.Thats works too. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-03-21 : 14:59:53
|
quote:
Originally posted by sodeep
Schedule the package to run every day.Thats works too.
How do I copy only the new/updated rows into the destination table, here source: sql2000 and destination is: sql2005. Thanks!! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-21 : 15:06:50
|
| Does the table have datetime column ? |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-03-21 : 15:12:03
|
| The table has datetime and also Stamp(timestamp datatype) columns. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-21 : 15:36:55
|
| Use like this:select * from [dbo].[tablename]where Datediff(day,date_column,Getdate()) <=1 |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-03-21 : 15:47:48
|
| Do I use like this:Firstday: Insert all the data from the table into the destination table.Second day onwards: Insert the data from the below query:select * from [dbo].[tablename] where Datediff(day,date_column,Getdate()) <=1 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-21 : 15:51:08
|
| Does the table gets updated and deleted as well? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-21 : 15:52:39
|
| You can go down to Hours/Minutes if the data is critical in DateDiffFunction. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-03-21 : 15:59:14
|
| No, only inserts/updates happen on the source table. Updating once every night is good enough. Thanks for all yr speedy responses. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-21 : 17:10:29
|
| Can also create a linked server then copy rows with 'insert ... select * from ... where date...'. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-21 : 17:13:10
|
| 1) Copy data with export/import wizard.2) Schedule the Query with Import/export wizard . |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-03-22 : 20:36:08
|
| Ok, I shall do the following: 1. using dts package will copy all the data from the table into the destination table.2. Will schedule the below query to run every night: select * from [dbo].[tablename] where Datediff(day,date_column,Getdate()) <=1Thanks very much for all the responses. |
|
|
|
|
Daily copy table from one server to another