Update master database

Question

Update master database

Alex Otten

Old Hand

Points: 396
More actions
October 19, 2005 at 12:43 am

#165999

Maybe there is an easy solution to my problem, but I can't see it.
I have a MSDE database on a production machine that will remove data older then 1 month every week.
The data that is removed is not save by the production system so I would like to copy this data to a central SQL server (I am not allowed to change the production system). At the moment I use DTS to copy the complete database to the central server, but there my problem occurs.
Either I have duplicate rows after every DTS action, or I have to save the data before the DTS action and get a lot of duplicates in my backups.
Is there a way to just add the new items (f.e. every hour) from the production database to the central server?
Any help would be appreciated
Alex

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Papillon Hall of Fame Points: 3303 More actions · Answer 1

Hi!!!

May be u r using join somewhere in your DTS so that duplicates row are inserted in ur backups.....or if it possible then filter in the DTS when data is inserting in central server....by putting DISTINCT keyword or GROUP BY...with having COUNT(*) > 1...like that...

Regards,

Papillon

Alex Otten Old Hand Points: 396 More actions · Answer 2

Shashank,

Thanks for your reply, but I don't think it is the solution. (or I don't understand it correct).

I don't have a query in the DTS package, I use the copy objects and data, and the the drop destination objects first. To my knowledge I can only use DISTINCT in a query on a specific column, and I want to distinct complete rows.

Maybe I have to use replication to realise my project, but I am not sure thats the solution.

Kind regards,

Alex

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Alex - instead of dropping the "destination objects" have you tried just copying the data with the "append data" option instead of the "replace existing data" ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Alex Otten Old Hand Points: 396 More actions · Answer 4

Sushila,

I did try the append data option, but then the data that already have been copied in a previous action will be inserted again. Then I have duplicate rows.

The append data is the correct way if I can exclude the data that already exist in the 'master'.

Kind regards,

Alex

Papillon Hall of Fame Points: 3303 More actions · Answer 5

Hi!!!!!

Can u paste ur insert query by which u r inserting records in central server....

Regards,

Papillon

Alex Otten Old Hand Points: 396 More actions · Answer 6

Shashank,

I do not use a query, I use the data import wizard. I assume that if I have to write a query, I would have to compare every row in every table before insertion. That can take a lot of time. When I have to do that every hour, the central server would be very busy with this task.

Kind regards,

Alex

philcart SSC-Forever Points: 47794 More actions · Answer 7

Alex

Are you able to create a llinked server to the production database? If so, you can simplify you DTS package to a series of delete/update/insert procedures for each table.

eg:

INSERT INTO DBO.MASTERTABLE ( FIELD1, FIELD2 )
SELECT FIELD1, FIELD2 FROM PRODSERVER.PRODDB.DBO.PRODUCTIONTABLE P
LEFT JOIN DBO.MASTERTABLE M ON P.FIELD1 = M.FIELD1
WHERE M.FIELD1 IS NULL

For the LEFT JOIN you would join on the primary key field(s).

--------------------
Colt 45 - the original point and click interface

Alex Otten Old Hand Points: 396 More actions · Answer 8

Phil,

Thanks, I will try this option. Looks like a good solution.

I will come back if this is not a satified solution. (it can take a while before I can try it)

For the moment, thank you all for you intrest and effort to help me out.

Kind regards,

Alex