| Author |
Topic |
|
zhtway1
Starting Member
10 Posts |
 Posted - 2010-08-20 : 01:47:32
|
| Hi,I am very new to sql server.I would like to copy data from "table1" to "table2" in the specific time. Which is the best way to do?Thankszhtway |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zhtway1
Starting Member
10 Posts |
Posted - 2010-08-20 : 01:51:01
|
| Hi,Thanks..How could I automate it everyday? Which is the best way? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zhtway1
Starting Member
10 Posts |
Posted - 2010-08-20 : 02:01:13
|
| Hi,In table1, I want to collect data for daily activities, then transfer to table2 to save for history. My sql server is 2005 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zhtway1
Starting Member
10 Posts |
Posted - 2010-08-20 : 02:08:43
|
| Hi again,Thanks for your reply.Actually, what I am doing is collection data with mobile device (sql compact server) then sync with sqlserver2005. So those data in sync tables want to store in history table. Is it suitable to use partition table with sync features?rgdszhtway |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-20 : 02:11:03
|
| I think zhtway1 is trying to take a history snapshot each night?table2 will grow large quickly if you copy all rows from Table1 to Table2 every night.Perhaps you will delete rows from Table2 after a short while? You could insert the Date/Time when the copy was made into Table2 (and use that to delete old/stale rows after a suitable period of time)An alternative would be to use an Audit table and to store records in that when they change - so rather than copying all records from Table1 to Table2 every day, whether they have changed or not, you could copy them only when they change (and store the date/time of the change). (SQL Server provides a method called a "Trigger" which will automatically "fire" every time a record is inserted or updated (or deleted) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-20 : 02:11:56
|
| Sorry, was posting whilst you were answering |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-20 : 02:13:11
|
| What happens to the original data? Is it deleted? (in which case, in effect, you want to MOVE the data from Table1 to Table2 - so Table1 is empty and can receive more data and Table2 is the permanent record of the data?) |
|
|
|
zhtway1
Starting Member
10 Posts |
Posted - 2010-08-20 : 02:21:14
|
| Hi Kristen,Yes, daily data is 200 on mobile sql and will purge every day. History table will store at lest 2 years. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-20 : 07:44:14
|
| Then I think you just want to INSERT INTO TargetTable (Col1, Col2, ...) SELECT Col1, Col2, ... FROM SourceTable and then delete those from the SourceTable.In SQL 2008 (maybe SQL2005 too) you should be able to use the OUTPUT phrase to delete the records that are successfully inserted, or you could do that as a separate job (preferable with a JOIN to TargetTable to prove that they are actually present before you delete them! |
|
|
|
zhtway1
Starting Member
10 Posts |
Posted - 2010-08-20 : 11:00:07
|
| Hi,Yes..you are right.I want to copy only when the condition is meet.I have a table with "PickUpID" field and "Status" field.Want to copy to History table only when ALL "Status" field has "Sign" value for each "PickUpID".If one record is still other value in "Status", I don't want to copy it yet.So do I just need to write code for job run as an agent?I don't actually know about the output phrase. I will do some research |
|
|
|
|
How to automate copying data