I'm migrating denormalized database to a normalized one.
I'm planning not to use DTS but do it this way
1. Use a front end to read tables to dataset
2. insert the dataset to a database view
3. the view has an instead-of-insert triggers that
shall handle the transforms and other logical needs
4. the view will log failed inserted records to logstables
if you are to choose between DTS and this approach which one will you choose
and whyHi
4. Certain errors are not able to be trapped in a SP or trigger. So the
whole batch gets rolled back automatically. This means your 'logged error'
too.
I would still use DTS and with SSIS from SQL Server 2005, it will be even
easier to do.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jose g. de jesus jr mcp, mcdba" wrote:
> I'm migrating denormalized database to a normalized one.
> I'm planning not to use DTS but do it this way
> 1. Use a front end to read tables to dataset
> 2. insert the dataset to a database view
> 3. the view has an instead-of-insert triggers that
> shall handle the transforms and other logical needs
> 4. the view will log failed inserted records to logstables
> if you are to choose between DTS and this approach which one will you choo
se
> and why
>|||Take a look at OpenRowSet rather than putting the schema into your new
database, you can use this command to connect two SQL Servers. Then you can
use a series of very big, but fast performing insert commands to migrate the
data one table at a time. Once the conversion is completed there's no
residual stuff from the old structure, unless of course you want them.
Regards
Colin Dawson
www.cjdawson.com
"jose g. de jesus jr mcp, mcdba"
<josegdejesusjrmcpmcdba@.discussions.microsoft.com> wrote in message
news:E06E117B-3E21-4C11-BE03-6721A1284DDF@.microsoft.com...
> I'm migrating denormalized database to a normalized one.
> I'm planning not to use DTS but do it this way
> 1. Use a front end to read tables to dataset
> 2. insert the dataset to a database view
> 3. the view has an instead-of-insert triggers that
> shall handle the transforms and other logical needs
> 4. the view will log failed inserted records to logstables
> if you are to choose between DTS and this approach which one will you
> choose
> and why
>|||Personally, I would perform the entire data transformation using T-SQL
scripts. There is no reason to develope a front end application or to use a
dataset, unless you love writing C# or VB.NET. If you pull the data to a
client side cursor, then it's going to run a lot slower.
"jose g. de jesus jr mcp, mcdba"
<josegdejesusjrmcpmcdba@.discussions.microsoft.com> wrote in message
news:E06E117B-3E21-4C11-BE03-6721A1284DDF@.microsoft.com...
> I'm migrating denormalized database to a normalized one.
> I'm planning not to use DTS but do it this way
> 1. Use a front end to read tables to dataset
> 2. insert the dataset to a database view
> 3. the view has an instead-of-insert triggers that
> shall handle the transforms and other logical needs
> 4. the view will log failed inserted records to logstables
> if you are to choose between DTS and this approach which one will you
> choose
> and why
>|||I need a very complex transform huhuhu.
I need to maintain relationship and
no data loss
table1 >>> target_table2, target_table3 and target_table4.
it must maintain relationship.
tt2 has a one to one relationship with tt3 and tt4
pk is guid
"jose g. de jesus jr mcp, mcdba" wrote:
> I'm migrating denormalized database to a normalized one.
> I'm planning not to use DTS but do it this way
> 1. Use a front end to read tables to dataset
> 2. insert the dataset to a database view
> 3. the view has an instead-of-insert triggers that
> shall handle the transforms and other logical needs
> 4. the view will log failed inserted records to logstables
> if you are to choose between DTS and this approach which one will you choo
se
> and why
>|||On Mon, 15 Aug 2005 07:09:05 -0700, jose g. de jesus jr mcp wrote:
>I need a very complex transform huhuhu.
>I need to maintain relationship and
>no data loss
>table1 >>> target_table2, target_table3 and target_table4.
>it must maintain relationship.
>tt2 has a one to one relationship with tt3 and tt4
>pk is guid
Hi jose,
Are you sure you need a guid column? Using surrogate keys has both pros
and cons; the currently fashionable choice appears to be to use a
surrogate key even if it doesn't have a benefit. And even if a surrogate
key would be beneficial in your case, odds are that an identity column
would be a better choice.
Anyway, that was not what you're asking. Assuming that you do indeed
need a guid surrogate key, you still don't need to pull data from the
server to a front and and back again for the transformation. Here's a
rough sketch of how you could do it in SQL Server only:
-- Step 1: Fill tt2 from main table
INSERT INTO tt2 (GuidKey, NaturalKey1, NaturalKey2, Other1, Other2)
SELECT NEWID(), NaturalKey1, NaturalKey2, Other1, Other2
FROM TableToBeTransformed
-- WHERE '
-- Step 2: Fill tt3, with reference to tt2.
INSERT INTO tt3 (Col1, Col2, Col3, RefToTT2)
SELECT a.Col1, a.Col2, a.Col3, b.GuidKey
FROM TableToBeTransformed AS a
JOIN tt2 AS b
ON b.NaturalKey1 = a.NaturalKey1
AND b.NaturalKey2 = a.NaturalKey2
-- WHERE '
-- Step 3: Same as step 2, but for tt4.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
