Hi,

I have a bulk insert question. I have a standard transform data task that
copies data from one database to another. As part of the insert, I am
sending some data that needs to be split out and transformed slightly and
inserted into another table (there could potentially be more than one insert
for each row). I'm wondering what the best way to accomplish this would be.
Can I do this as part of the Active X transform script for the task? Or
would I be better having a separate step that runs after the copy and
processes the new rows, or would an insert trigger on the destination table
be better?

Regards,

Tim.

You could stage the rows on the destination and then perform some TSQL to do
this.

You could adapt this article

Processing The Same Row More Than Once
(http://www.sqldts.com/default.aspx?266)

the problem I can see with insert triggers would be that you are inserting
into the same table right?


--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Tim Payne" <tim (AT) branded3 (DOT) com> wrote

Thanks for the link, it's not quite what I'm after though. I'll explain it
in more detail. I have some tracking data that is stored onm various
servers. Every night a DTS task pumps the tracking data into the main work
table of the reporting database. One of the tracking fields contins
delimited information that needs to be split out into a separate table. The
table isn't particularly normalised, as it's geared towards running the
tracking data more than anything else. For each row in the tracking data
there may be either no data for the second table, or up to 10 rows to insert
into the second table (although this could change).

Would I actually be better off processing this data separately? Say after
the main task, I do a separate query to pull out just the rows with the
delimited information in, and then use the method in the article you linked
to as a way of breaking out the delimited data as a separate process. I'm
thinking that might actually be a good way of doing it, as there are
potentially several hundred thousand rows of tracking data each time the
task is run, of which less than 10% will normally contain the delimited
data. That way the main pump task will run nice and quickly (as it won't
have to check for and process the delimited data for each row).

Regards,

Tim.

Two passes over the data (One for Master, One for Child) may be the best
option here yes.

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Tim Payne" <tim (AT) branded3 (DOT) com> wrote