Data Conversion from Access to SQL 2005

[Greg]

This may not be the proper forum to ask this question, but I am totally stuck
on something that I need someone elses input on.

I just migrated my Access app to SQL Server 2005 and have found the the
learning curve for the SQL 2005 DTS Equivilant is steep and something I don't
have time for right now. Plus, I do not have access to SQL 2000 to do DTS.
Anyway, I am handling my data conversion using Stored Procedure and Code.

Here is my question.

I have a table "tblTableName" that has 20 fields that should have been
normalized in the first place. (i.e. 1 lookup table). These fields are named
"Oper1, Oper2, Oper3, Oper4..." for a total of 20. I've changed this to
OperID and now have a lookup table called tblOper. During my data conversion
I am checking the value of Oper1 against the tblOper table to retrieve the
OperID value. If it exists, I return it, otherwise I add the Oper1 value and
return the new OperID.

The problem I have it is this is very process intensive. In my example, the
table has 26,000 records. Not very big. But, because of all the processes
that are occuring it takes 7.2 hours to convert one table based on my
calculations. Needless to say, I need to find a method that will insert the
records faster. ANY SUGGESTIONS. (SORRY TO THE CAPS, MY KEYBOARD JUST GOT
STUCK IN ALLCAPS).

 

[Sylvain Lafontaine]

Copy the whole table directly to the SQL-Server and perform the conversion
(normalization) directly on the SQL-Server using a stored procedure. This
will be much faster then using VBA code and transmitting Select and Insert
statements for each field of every rows over the wire.

Don't forget to add the proper index on the lookup table tblOper.

For your ALL CAPs problem, check the Caps Lock key on your keyboard.

 

[Klaus Oberdalhoff]

Hi,

I do not have access to SQL 2000 to do DTS.

http://www.microsoft.com/downloads/...aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

You know, that the feature pack contains DTS for 2005 ?
Microsoft SQL Server 2000 DTS Designer Components
and
Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions
of Data Transformation Services 2000 runtime (DTS),

OK, it´s depreciated, but it should work ...

 

[Peter Yang [MSFT]]

Hello Greg,

You could import the table from Access to SQL Server 2005 by using the
"Import data" function.

Right click a database->tasks->Import data, and select Microsoft Access
connection in Data source type, and you could then import the table as you
want. Then as Sylvain mentioned, you could use SP to do normalization
inside SQL Server.

If you have any update or need further help from us, please feel free to
let's know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.