MS SQL Server DTS - DST to copy from one database to another, but using a JOIN across them.

Author

DST to copy from one database to another, but using a JOIN across them.

Chubby Arse

2006-01-26, 4:58 pm

Hi All,

Firstly apologies for the obscure subject line.

The task I am try to achieve is to copy information from Database A on
Server 1 to Database B on Server 2.

I would like this to be a DTS task (so that Many tables can be updated
in the target database). However my knowledge of DTS is limited to the
single database.

For each table I would firstly INSERT into the target table the missing
rows using an outer join and an IS NULL on the primary key of the
target table. Secondly I would execute an UPDATE to update the fields
linking on the primary keys.

The problem I have, is that none of the DTS tasks allow me to link to
the destination database without hardcoding the
server.database.owner.table

Is there anyway I can do this, or do I have to go down the route of
using linked servers?

I've tried that with success (see below), but I just can't help but
wonder if there is a slicker way.

Also, when I run the following script in query analyzer, it doesn't
work unless I execute the sp_addlinkedserver line sepearately and then
run the entire script. The error it gives is:

"Could not find server 'DEVTEST1' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers."

It's almost as if in alter the procedure it is actually runnning the
line??????

Cheers

Alex

*****Example Script*******

ALTER PROC proc_ImportData

AS

--Add the connection to the CRM Database server.
EXEC sp_addlinkedserver
'DEVTEST1',
N'SQL Server'

INSERT INTO tblCompany(CompanyID
, CompanyName, PrimaryPerson)
SELECT Comp_CompanyID, Comp_Name, RTRIM(Pers_FirstName
) + ' ' +
RTRIM(Pers_LastName)

FROM DEVTEST1.CRM.dbo.Company AS Company
LEFT JOIN DEVTEST1.CRM.dbo.Person AS Person ON
Company. Comp_PrimaryPersonID
= Person.Pers_PersonID
LEFT JOIN tblCompany ON Company.Comp_CompanyID =
tblCompany.CompanyID
WHERE tblCompany.CompanyID IS NULL
ORDER BY Company.Comp_Name

UPDATE tblCompany
SET CompanyName = Comp_Name,
PrimaryPerson = RTRIM(Pers_FirstName
) + ' ' + RTRIM(Pers_LastName)

FROM DEVTEST1.CRM.dbo.Company AS Company
JOIN tblCompany ON Company.Comp_CompanyID = tblCompany.CompanyID
LEFT JOIN DEVTEST1.CRM.dbo.Person AS Person ON
Company. Comp_PrimaryPersonID
= Person.Pers_PersonID

--Remove the connection to the CRM Database server.
EXEC sp_dropserver 'DEVTEST1'

********************
**********

Allan Mitchell

2006-01-29, 3:23 am

Hello Chubby,

Linked servers or OPENDATASOURCE seem to be the way forward here. There
is no cool way to do this in DTS 2000

Allan

> Hi All,
>
> Firstly apologies for the obscure subject line.
>
> The task I am try to achieve is to copy information from Database A on
> Server 1 to Database B on Server 2.
>
> I would like this to be a DTS task (so that Many tables can be updated
> in the target database). However my knowledge of DTS is limited to the
> single database.
>
> For each table I would firstly INSERT into the target table the
> missing rows using an outer join and an IS NULL on the primary key of
> the target table. Secondly I would execute an UPDATE to update the
> fields linking on the primary keys.
>
> The problem I have, is that none of the DTS tasks allow me to link to
> the destination database without hardcoding the
> server.database.owner.table
>
> Is there anyway I can do this, or do I have to go down the route of
> using linked servers?
>
> I've tried that with success (see below), but I just can't help but
> wonder if there is a slicker way.
>
> Also, when I run the following script in query analyzer, it doesn't
> work unless I execute the sp_addlinkedserver line sepearately and then
> run the entire script. The error it gives is:
>
> "Could not find server 'DEVTEST1' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers."
>
> It's almost as if in alter the procedure it is actually runnning the
> line??????
>
> Cheers
>
> Alex
>
> *****Example Script*******
>
> ALTER PROC proc_ImportData
>
> AS
>
> --Add the connection to the CRM Database server.
> EXEC sp_addlinkedserver
> 'DEVTEST1',
> N'SQL Server'
> INSERT INTO tblCompany(CompanyID
, CompanyName, PrimaryPerson)
> SELECT Comp_CompanyID, Comp_Name, RTRIM(Pers_FirstName
) + ' ' +
> RTRIM(Pers_LastName)

> FROM DEVTEST1.CRM.dbo.Company AS Company
> LEFT JOIN DEVTEST1.CRM.dbo.Person AS Person ON
> Company. Comp_PrimaryPersonID
= Person.Pers_PersonID
> LEFT JOIN tblCompany ON Company.Comp_CompanyID =
> tblCompany.CompanyID
> WHERE tblCompany.CompanyID IS NULL
> ORDER BY Company.Comp_Name
> UPDATE tblCompany
> SET CompanyName = Comp_Name,
> PrimaryPerson = RTRIM(Pers_FirstName
) + ' ' + RTRIM(Pers_LastName)

> FROM DEVTEST1.CRM.dbo.Company AS Company
> JOIN tblCompany ON Company.Comp_CompanyID = tblCompany.CompanyID
> LEFT JOIN DEVTEST1.CRM.dbo.Person AS Person ON
> Company. Comp_PrimaryPersonID
= Person.Pers_PersonID
> --Remove the connection to the CRM Database server.
> EXEC sp_dropserver 'DEVTEST1'
> ********************
**********
>

Sponsored Links