Hi,

Is there any differences in performance between DTS and T-SQL when
copying tables from one db to another without any transforms? I'd like
to keep everything as simple as possible, and doing a distributed query
in T-SQL seems like the best option. Am I missing something, or is DTS
totally overkill for this task?

regards,
Knut Eldhuset

DTS give you power through its flexibility. If you can manage things
through T-SQL, you're better off, since you don't have to learn DTS. As for
a distributed query, it depends. Use stored procs to retrieve the data from
the source and then you can do an INSERT EXEC:

insert MyTable
exec OtherServer.OtherDB.dbo.MyProc

I'd avoid doing raw SELECT's across the linked server:

insert MyTable
select * from OtherServer.OtherDB.dbo.OtherTable

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<knut.eldhuset (AT) gmail (DOT) com> wrote

Hi,

Is there any differences in performance between DTS and T-SQL when
copying tables from one db to another without any transforms? I'd like
to keep everything as simple as possible, and doing a distributed query
in T-SQL seems like the best option. Am I missing something, or is DTS
totally overkill for this task?

regards,
Knut Eldhuset

Tom Moreau wrote:
Well, what I need to do is not a simple select, but basically this:

create table #temp
(
NewID uniqueidentifier not null,
OldID uniqueidentifier not null
)

insert into #temp
select NewID(), E.ID from OtherServer.OtherDB.dbo.Entries E
where E.ID not in (select T.OldID from #temp T)

The goal is to collect rows from the Entries table on multiple servers
into the #temp table on the master server, thereby creating a mapping
table. Some IDs in the different Entries tables might be equal, so the
"not in" part is needed to ensure that no duplicates are created.

What is the difference between executing a stored proc on the remote
server and doing a simple select? What about distributed joins like
described above?

I don't see what I would gain by using DTS, but my understanding of DTS
might be lacking. Because I also have the added complexity of having to
create DTS packages through C#, I really want to avoid DTS.

regards,
Knut Eldhuset

Well, in this case, since you are using linked servers and have to correlate
data being transferred, there may not necessarily be any advantage in using
a stored proc. Depending on how frequently you have to do this, you could
consider replication and then do the correlated subquery across local
tables. If you're not doing this frequently, then you may just have to take
the performance impact just for those times.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<knut.eldhuset (AT) gmail (DOT) com> wrote


Tom Moreau wrote:
Well, what I need to do is not a simple select, but basically this:

create table #temp
(
NewID uniqueidentifier not null,
OldID uniqueidentifier not null
)

insert into #temp
select NewID(), E.ID from OtherServer.OtherDB.dbo.Entries E
where E.ID not in (select T.OldID from #temp T)

The goal is to collect rows from the Entries table on multiple servers
into the #temp table on the master server, thereby creating a mapping
table. Some IDs in the different Entries tables might be equal, so the
"not in" part is needed to ensure that no duplicates are created.

What is the difference between executing a stored proc on the remote
server and doing a simple select? What about distributed joins like
described above?

I don't see what I would gain by using DTS, but my understanding of DTS
might be lacking. Because I also have the added complexity of having to
create DTS packages through C#, I really want to avoid DTS.

regards,
Knut Eldhuset