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 |
|
mchohan
Starting Member
39 Posts |
 Posted - 2009-07-07 : 10:27:43
|
| Hi,I used the import/export wizard to import all the tables from say Database A (on Server A) to Database B (on server B). I've then got a job that runs the above package each night.Problem: I want to only populate Database B with only newly created rows from Database A overnight. I don't want the entire database table coming across, only the new data.thanks in advance... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-08 : 12:41:00
|
| do you have an audit column in your table to identify changed data? |
 |
|
|
mchohan
Starting Member
39 Posts |
Posted - 2009-07-09 : 10:47:06
|
| No, I've just got ssis setup now using BIDS. It only imports newly created rows. However as you have guessed, I need to update existing rows already in Database B... any ideas? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-09 : 10:48:56
|
| you need to have a audit column for thatAlternatively, you can use SCD wizard if you've PK column in your table. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-09 : 10:50:03
|
| also see this if you chose latterhttp://blogs.tallan.com/datareflections/2008/08/14/the-scd-transformation-in-ssis/ |
|
|
|
mchohan
Starting Member
39 Posts |
Posted - 2009-07-09 : 11:36:37
|
| HiI think ETL might not be the best solution to keep a replica copy of the db. I think replication might be best suited for this. I'm going to go down the replication route. The only issue here is that there are developers that will sometimes want to makes schema changes to database A... this won't be possible with a replication link in place. I'll need a good way to allow them to make schema changes to Database A which is then transfered to Database B via transactional replication....any suggestions welcome.thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-12 : 08:34:50
|
quote:
Originally posted by mchohan
HiI think ETL might not be the best solution to keep a replica copy of the db. I think replication might be best suited for this. I'm going to go down the replication route. The only issue here is that there are developers that will sometimes want to makes schema changes to database A... this won't be possible with a replication link in place. I'll need a good way to allow them to make schema changes to Database A which is then transfered to Database B via transactional replication....any suggestions welcome.thanks.
your first post said you're using import/export wizard thats why i suggested this. you can very well use transactional replication. but you might need to add new columns to publication when schema changes |
|
|
|
|
|
|
Import Only New Rows