Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marcus72
Starting Member
1 Post |
 Posted - 2007-06-22 : 14:54:04
|
| Hi, I want to run a job in SQL 2005 Developer edition, where I transfer data from a working table in a db into an 'archive' table with the same schema on a second db every half hour. But I only want to transfer data that's new data, so I want to keep all the data that is there but only add new records that have changed in the last half hour based on the PK. For instance:select * into tbl2from tbl1--not sure what to write after this...where tbl1.pk doesn't exist in tbl1I would appreciate your help as my sql coding skills are quite amateurish. Thank you. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-22 : 15:09:03
|
| You might need a datetime column in each of the tables with the value defaulted to getdate(). So your job will pick up those records where DateColumn > (lastrundate).Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
cvraghu
Posting Yak Master
187 Posts |
Posted - 2007-06-22 : 18:48:20
|
quote:
Originally posted by marcus72
I want to keep all the data that is there but only add new records that have changed in the last half hour based on the PK. For instance:select * into tbl2from tbl1--not sure what to write after this...where tbl1.pk doesn't exist in tbl1
1. You cannot use "Select * Into " syntax, since it will try to create the table again and error out. Use "Insert into table .."2. You cannot identify "Changed" records using PK. You can identify only new records. As Dinakar has mentioned, you need a date column like "lastupdateddate". You can default it to getdate(). But make sure it is updated with datetime last it was updated, so that you can query against it.3. If you are sure that you do not care about changes to already archived records, you can eliminate exisiting records using "NOT IN" or NOT EXISTS".. ex WHERE tbl1.PK NOT IN (SELECT PK FROM tbl2)Read in Books online. |
|
|
|
|
|
|
Insert Into --new records only