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 |
|
lparker
Starting Member
1 Post |
 Posted - 2003-06-12 : 20:56:42
|
| I've successfully inserted all records from one table y to table x. Data is periodically being copied to table y....I would like to keep table x up-to-date with y, so I'll need to only insert the new records from table y into table x. Whats the syntax for doing this?Edited by - merkin on 06/12/2003 22:32:41 |
|
|
darinh
Yak Posting Veteran
58 Posts |
Posted - 2003-06-12 : 21:01:21
|
| Have a search on here or in Books Online for info on triggers |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-06-13 : 06:43:22
|
| Triggers are a good idea like darinh suggested, if you would like to keep both tables in sync.If you can afford a little bit of latency, you could add a bit column to table Y, that could be named IsNewRecord. The value could have default of 1 for all newly inserted records. You could then schedule a job to run a query to insert all the records where this flag is equal to 1 into table X and reset the value to 0. A longer solution but your sync process will be done in batches rather than real-time.Owais |
|
|
|
sherrer
64 Posts |
Posted - 2003-06-13 : 22:29:13
|
| It would be more efficient to check the existance of the records in y and insert into x. You need to have a key field in both tables and would need an index on the field in y to make it work correctly. You can use it as a job or in a trigger depending on if you need real time or can batch. Batching is much faster if you have a large number of transactions.insert into x (Key, T1, T2, T3, ...)select Key, T1, T2, T3, ... from ywhere not exists (select * from x where x.key = y.key)This keeps from having to search on a bit field (not a good idea) and it keeps you from having to update table y everytime you need to sync table x. |
|
|
|
|
|
|
|
|
Inserting records in a table