Update files based using values from another file

#1

I'm fairly new at DTS. I have a customer file with a field for turnover in
it plus a second file with turnover figures in it for each customer. For
each customer I want to update the turnover field linking the two files on
customerID.

What's the best way to do this?

Thanks - Dave

#2

When you say files do you mean tables in the database or physically
separated files ?

I'm not an export yet - either .... but if it's tables in the database it
can be solved by using mdx .. perhaps it could be solved using triggers /
stored procedures as well. If it's tables I interpret your description as if
you have a table with fewer records where you would like to aggrete the
turnover and put it in this table.

If this is what you want you could use dts with an execute sql statement
like this:

Update table1 set turnover = (select sum(turnover) from table2 where
table2.customerid = table1.customerid) from table1

If it's phyisically separated files I think I would put it into tables and
do something similar. The question here is however if you need to put it
back into a file again afterwards.

\Michael Vardinghus

Dave Williams <david_n_williams (AT) lineone (DOT) net> wrote

Quote:

I'm fairly new at DTS. I have a customer file with a field for turnover in
it plus a second file with turnover figures in it for each customer. For
each customer I want to update the turnover field linking the two files on
customerID.

What's the best way to do this?

Thanks - Dave

#3

It should have said solved by using DTS...sorry...

Michael Vardinghus <mivar (AT) wmdata (DOT) dk> wrote

Quote:

When you say files do you mean tables in the database or physically
separated files ?

I'm not an export yet - either .... but if it's tables in the database it
can be solved by using mdx .. perhaps it could be solved using triggers /
stored procedures as well. If it's tables I interpret your description as
if
you have a table with fewer records where you would like to aggrete the
turnover and put it in this table.

If this is what you want you could use dts with an execute sql statement
like this:

Update table1 set turnover = (select sum(turnover) from table2 where
table2.customerid = table1.customerid) from table1

If it's phyisically separated files I think I would put it into tables and
do something similar. The question here is however if you need to put it
back into a file again afterwards.

\Michael Vardinghus

Dave Williams <david_n_williams (AT) lineone (DOT) net> wrote in message
news:OdlqIzuGEHA.1152 (AT) TK2MSFTNGP10 (DOT) phx.gbl...
I'm fairly new at DTS. I have a customer file with a field for turnover
in
it plus a second file with turnover figures in it for each customer. For
each customer I want to update the turnover field linking the two files
on
customerID.

What's the best way to do this?

Thanks - Dave

#4

Should have said using DTS (not MDX..) - sorry

Michael Vardinghus <mivar (AT) wmdata (DOT) dk> wrote

Quote:

When you say files do you mean tables in the database or physically
separated files ?

I'm not an export yet - either .... but if it's tables in the database it
can be solved by using mdx .. perhaps it could be solved using triggers /
stored procedures as well. If it's tables I interpret your description as
if
you have a table with fewer records where you would like to aggrete the
turnover and put it in this table.

If this is what you want you could use dts with an execute sql statement
like this:

Update table1 set turnover = (select sum(turnover) from table2 where
table2.customerid = table1.customerid) from table1

If it's phyisically separated files I think I would put it into tables and
do something similar. The question here is however if you need to put it
back into a file again afterwards.

\Michael Vardinghus

Dave Williams <david_n_williams (AT) lineone (DOT) net> wrote in message
news:OdlqIzuGEHA.1152 (AT) TK2MSFTNGP10 (DOT) phx.gbl...
I'm fairly new at DTS. I have a customer file with a field for turnover
in
it plus a second file with turnover figures in it for each customer. For
each customer I want to update the turnover field linking the two files
on
customerID.

What's the best way to do this?

Thanks - Dave

#5

Should have been DTS...not MDX ... sorry

Michael Vardinghus <mivar (AT) wmdata (DOT) dk> wrote

Quote:

When you say files do you mean tables in the database or physically
separated files ?

I'm not an export yet - either .... but if it's tables in the database it
can be solved by using mdx .. perhaps it could be solved using triggers /
stored procedures as well. If it's tables I interpret your description as
if
you have a table with fewer records where you would like to aggrete the
turnover and put it in this table.

If this is what you want you could use dts with an execute sql statement
like this:

Update table1 set turnover = (select sum(turnover) from table2 where
table2.customerid = table1.customerid) from table1

If it's phyisically separated files I think I would put it into tables and
do something similar. The question here is however if you need to put it
back into a file again afterwards.

\Michael Vardinghus

Dave Williams <david_n_williams (AT) lineone (DOT) net> wrote in message
news:OdlqIzuGEHA.1152 (AT) TK2MSFTNGP10 (DOT) phx.gbl...
I'm fairly new at DTS. I have a customer file with a field for turnover
in
it plus a second file with turnover figures in it for each customer. For
each customer I want to update the turnover field linking the two files
on
customerID.

What's the best way to do this?

Thanks - Dave

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Update files based using values from another file

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Update files based using values from another file - 04-05-2004 , 04:02 AM

Re: Update files based using values from another file - 04-06-2004 , 01:48 PM

Re: Update files based using values from another file - 04-06-2004 , 02:14 PM

Re: Update files based using values from another file - 04-06-2004 , 02:15 PM

Re: Update files based using values from another file - 04-06-2004 , 02:16 PM