Optimization tips for Cursor and Scope_Identity

...using SQL 2K
...This is for a data migration project which involves the splitting of a
flat file into two or more tables with relationships. The migration will
happen over time and not at one time.
...My SQL Server/DB experience up to this point has been primarily focused
on client side request, but am pretty short on optimization... so I need som
e
direction here. I can research via Google, BOL and forums, but I don't even
know where to start.
I am currently using a cursor to walk through rows in tblMigration and build
an insert statement for tblParent. I am using the cursor becuase I need to
capture the new identity value (SCOPE_IDENTITY) after each insert into
tblParent and use it to migrate the rest of the records destined for the
various child tables in the new schema.
I am moving data from one server to another and have set up a linked server.
currently a migration of 53,000 records is taking 42 min (on my dev box:
WINXP, 1GB ram, 2 GHZ).
I originally wanted to do the following, but was told this would play havoc
with replication
1. ALTER tblParent ADD old_id BIGINT
2. INSERT tblParent (field1,field2,old_id) SELECT field1, field2, id FROM
tblMigration WHERE field1='parent'
3. do remaing migration relating the tblParent.old_id to tblMIgration.id fie
ld
4. and then drop the old_id collumn from tblParent
Once again, I have not posted actual TSQL because it would be very long and
I just need the pointer in the right direction... but if that would help, le
t
me know.
thanks
KevinHavoc with replication? How?

> I originally wanted to do the following, but was told this would play
> havoc
> with replication
> 1. ALTER tblParent ADD old_id BIGINT
> 2. INSERT tblParent (field1,field2,old_id) SELECT field1, field2, id FROM
> tblMigration WHERE field1='parent'
> 3. do remaing migration relating the tblParent.old_id to tblMIgration.id
> field
> 4. and then drop the old_id collumn from tblParent
Either way, if you have a natural key in the field1 or field2 combination
that you can use for getting the surrogate (identity) key that is even
better.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:1A0B0471-24CF-414D-AE0B-5EF830993828@.microsoft.com...
> ...using SQL 2K
> ...This is for a data migration project which involves the splitting of a
> flat file into two or more tables with relationships. The migration will
> happen over time and not at one time.
> ...My SQL Server/DB experience up to this point has been primarily focused
> on client side request, but am pretty short on optimization... so I need
> some
> direction here. I can research via Google, BOL and forums, but I don't
> even
> know where to start.
> I am currently using a cursor to walk through rows in tblMigration and
> build
> an insert statement for tblParent. I am using the cursor becuase I need
> to
> capture the new identity value (SCOPE_IDENTITY) after each insert into
> tblParent and use it to migrate the rest of the records destined for the
> various child tables in the new schema.
> I am moving data from one server to another and have set up a linked
> server.
> currently a migration of 53,000 records is taking 42 min (on my dev box:
> WINXP, 1GB ram, 2 GHZ).
> I originally wanted to do the following, but was told this would play
> havoc
> with replication
> 1. ALTER tblParent ADD old_id BIGINT
> 2. INSERT tblParent (field1,field2,old_id) SELECT field1, field2, id FROM
> tblMigration WHERE field1='parent'
> 3. do remaing migration relating the tblParent.old_id to tblMIgration.id
> field
> 4. and then drop the old_id collumn from tblParent
> Once again, I have not posted actual TSQL because it would be very long
> and
> I just need the pointer in the right direction... but if that would help,
> let
> me know.
> thanks
> Kevin
>|||"Louis Davidson" wrote:

> Havoc with replication? How?
>
I was told, by our SQL DBA, that ad-hoc schema changes would not be allowed
with replication. Now I am not certain if he meant
1. that replication, by design, would cause any schema changes to fail
2. that schema changes would be in violation of business rules because they
would break replication and thus I was not to do this or
3. that replication would destroy any schema changes I made by overwritting
them
I am working on the understanding, a bit fuzzy as it is, that replication
essentially is a manage procedure for copying database schema and data
changes from one server to another as a form of backup and/or security.
Suffice it to say, I was explicity told "No schema changes". Personnaly I
don't see the big deal wth adding collumns as this will be invisible to any
client apps unless they are calculating the ordinal position of particular
collumns... which would be pretty insane.
In either case saying that a migration of a midsized client would take 40
minutes got a consession. I will throwing my old_id into an existing collum
n
that would never be populated by legacy records on the most critical part of
the migration.
I will still have to use cursors for some of the smaller tables.
If there is a way to optimize (hints etc), I would love the input.
thanks.
Kevin
"Louis Davidson" wrote:

> Havoc with replication? How?
>