I am using SQL Server 2000 and I have two user databases defined (ROE &
ROE6). The ROE db has a table named Lots and it contains a column titled
"OrigLot". Can I use DTS to retreive a portion (SELECT OrigLot FROM Lots
where OrigLot >= 233) in ROE.Lots table and overwrite a complete column in
ROE6.Lots.Lot column with that data? The data values in ROE6.Lots.Lot column
contain values of type varchar like 200-2330, 200-2340, etc. The
ROE.Lots.OrigLot contains value of type varchar like 233, 234, etc. Any help
will be greatly appreciated. TIA, Jim

you can do this with the help of update statement. See following example.
Assume following tables are located in different databases.

create table ROE_Lots
( idd int primary key,
origlot varchar(500))
go
create table ROE6_Lots
(idd int primary key,
Lot varchar(500))
go

--insert dummy values.
insert into roe_lots values(1, '231');
insert into roe_lots values(2, '233');
insert into roe_lots values(3, '234');

insert into roe6_lots values(1, '200-2310');
insert into roe6_lots values(2, '200-2330');
insert into roe6_lots values(3, '200-2340');

--update statement.

update roe6_lots
set roe6_lots.lot = (select a.origlot from roe_lots a
where a.idd = roe6_lots.idd)

If you want to do this task on recurring basis, then create a DTS package
with Execute SQL Task. and put the update statement in it.

--Vishal.


"Jim Richards" wrote:

by adding the condition of "OrigLot >= 233" your update statement will look
like this:

update roe6_lots
set roe6_lots.lot = (select a.origlot from roe_lots a
where a.idd = roe6_lots.idd and a.origlot > 233)
where exists
(select * from roe_lots a
where a.idd = roe6_lots.idd and a.origlot > 233)


"Vishal Parkar" wrote: