DTS data transfer and Update

My DTS package transfers data mostly codes from temp to master table. The master table has 10 description fields for each of the codes. These description fields are then populated using update statements joined with 10 description tables. However, the update process is so inefficient since a single update statement takes several hours to finish.

The second option is to populate description fields as codes are transferred from temp to master table using 10 left outer joins with description tables. But the result was unexpected. It's giving me more than twice as many records as there are in the temp table.

What would be the best (efficient) approach for this situation? Would greately appreciate any help/thoughts.

thanks.I think I would create a view, unload the vie and bcp the data into the table in native format

Do the code tables contain keys?|||a single update statement takes several hours to finish

Then you must be doing something wrong...

The master table has 10 description fields

This sounds like awful design! Fancy posting the DDL of the table in question?sql

DTS and Excell

Help.
Can someone help me with a DTS? I have a curser that creates a temp
table and then sever select statments from the tmp table. I am tryin to
place each out put from the many select statements in its own excel
worksheet.Hi
"TG" wrote:
> Help.
> Can someone help me with a DTS? I have a curser that creates a temp
> table and then sever select statments from the tmp table. I am tryin to
> place each out put from the many select statements in its own excel
> worksheet.
The way I would probably do this would be for each select statement to
require it's own transformation with the excel file as the datasource and the
destination table being the datasheet that you are inserting into.
HTH
John

DTS and #temp tables

I have a rather complex SP that creates a #temp table and then populates tha
t
table using several select/inserts/update statements. I need the results of
this #table to be exported to external text file nightly so that our
mainframe FTPs can grab it.
DTS apparently wont let me use #temp tables. I get invalid object errors.
Ive heard of global ##temp, but even changing the table all the references
to be ##table isn’t solving the problem.
Is there commands I can place in the SP to create the external file without
using DTS, or can I define the #temp table in a way that DTS can see it?
Help appreciated
--
JP
.NET Software DeveloperJP,
You are running into problems with the scope of a #temp table. It is
available from the creating code level DOWN. Anything above the block of
code, stored procedure, etc will not be able to see this table. Likewise,
anything that runs in some other connection, for example one used by a DTS
package, will not be able to see the #temp table.
In the SQL Books Online, read the discussion under the CREATE TABLE command
on Temporary Tables. It discusses the scope and life of both kinds of
temporary tables.
RLF
"JP" <JP@.discussions.microsoft.com> wrote in message
news:66B3FF76-EB09-46A0-A9FF-2F6AA24E2EAB@.microsoft.com...
>I have a rather complex SP that creates a #temp table and then populates
>that
> table using several select/inserts/update statements. I need the results
> of
> this #table to be exported to external text file nightly so that our
> mainframe FTPs can grab it.
> DTS apparently wont let me use #temp tables. I get invalid object errors.
> Ive heard of global ##temp, but even changing the table all the
> references
> to be ##table isn't solving the problem.
> Is there commands I can place in the SP to create the external file
> without
> using DTS, or can I define the #temp table in a way that DTS can see it?
> Help appreciated
> --
> JP
> .NET Software Developer
>|||Hi,
I would suggest you to use UDF instead of #TempTable so it is able to be
exported
Ed
"JP" wrote:

> I have a rather complex SP that creates a #temp table and then populates t
hat
> table using several select/inserts/update statements. I need the results o
f
> this #table to be exported to external text file nightly so that our
> mainframe FTPs can grab it.
> DTS apparently wont let me use #temp tables. I get invalid object errors.
> Ive heard of global ##temp, but even changing the table all the reference
s
> to be ##table isn’t solving the problem.
> Is there commands I can place in the SP to create the external file withou
t
> using DTS, or can I define the #temp table in a way that DTS can see it?
> Help appreciated
> --
> JP
> .NET Software Developer
>