DTS - I have 2 Servers - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DTS - I have 2 Servers

Author

Topic

ragh
Starting Member

34 Posts

Posted - 2003-01-18 : 00:41:25

I have two servers
1. Infoprod1
2. Infoprod2

I have two databases
Db1 sits in infoprod1
Db2 sits in infoprod2

My table structure of db1 is like this
--------------------------------------
Field Name Table Name Tb_M_Issues
Issue_ID Primary Key Auto Gen key
Iss_Volume
Iss_Number
Iss_Part
Iss_Supplimentry
Journal_ID
Iss_Date
Iss_Month
Iss_Year
Sort_Order
Last_Modified
Active_flag
======================================================================

My table structure of db2 is like this
--------------------------------------
Field Name Table Name Tb_M_Issues
Issue_ID Primary Key Auto Gen key
Iss_Volume
Iss_Number
Iss_Part
Iss_Supplimentry
Journal_ID
Iss_Date
Iss_Month
Iss_Year
Sort_Order
Last_Modified
Active_flag
Iss_Source Source name for eg: db1
Source_ID Issue id of db1
----------------------------------------------------------------------

Now I have to write one dts script the data from infoprod1 should be transferred to infoprod2 daily.

There is a chance of modifying of existing record in db1 so this record should b updated instead of inserting it.
Please let me know how to do it.
Hope u r clear abt this. If u hv any doubt pl. ask me.

regards

Ragh

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-18 : 14:04:49

Well you can either use dts or linked the servers.

If you dts create a temp table in the db2 which contains the table in db1.

What I would suggest is that you do three queries.

First delete where it doesn't exist any longer or mark inactive
ex:

/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor LEFT JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title IS NULL

your where clause should specify where pk's of right table is null

Next Insert where it doesn't exist db1.table left join db2.table
where db2.table.pkid is Null

--INSERT...SELECT example
USE pubs
INSERT author_sales
SELECT authors.au_id
FROM authors LEFT JOIN titleauthor
ON authors.au_id = titleauthor.au_id
WHERE titleauthor.au_id IS NULL

your where clause should specify where pk's of right table is null

Then update (last for referential integrity issues when updating multiple tables)
ex:
UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

make sure you're where clause specifies how to determine when two records are equal

Edited by - ValterBorges on 01/18/2003 14:06:49

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources