Help importing legacy FoxPro db into SQL 2000

Greetings,
Any help/tips appreciated. I'm charged with importing a legacy FoxPro
database appliction into SQL 2000 and building a new .NET front-end for it.
I've imported .mdb, Excel, text files, other MS SQL data, etc in the past,
but the FoxPro stuff is rather different. I see the .dbf files (which I'm
guessing will import as tables), but when I try to import, the DSN import bo
x
comes up. I try 'Visual FoxPro drivers' but they're not installed. A little
research shows MS stopped bundling FoxPro drivers in MDAC 2.6.
Does anyone have experience with this type of import?
Thanks in advance!
jgHi Johnny,
Download and install the FoxPro and Visual FoxPro OLE DB data provider from
msdn.microsoft.com/vfoxpro/downloads/updates.
When you set up your import you'll want to determine whether you have a
database container (a DBC file is present in the directory where the DBFs
are) or free tables. With a DBC you connect to the DBC and with free tables
you just connect to the directory they are in.
After that it's just like importing any other OLE DB compliant data.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"johnnyG" <johnnyG@.discussions.microsoft.com> wrote in message
news:CBE76BA7-C433-4EDD-8C73-123690CAA92E@.microsoft.com...
> Greetings,
> Any help/tips appreciated. I'm charged with importing a legacy FoxPro
> database appliction into SQL 2000 and building a new .NET front-end for
> it.
> I've imported .mdb, Excel, text files, other MS SQL data, etc in the past,
> but the FoxPro stuff is rather different. I see the .dbf files (which I'm
> guessing will import as tables), but when I try to import, the DSN import
> box
> comes up. I try 'Visual FoxPro drivers' but they're not installed. A
> little
> research shows MS stopped bundling FoxPro drivers in MDAC 2.6.
> Does anyone have experience with this type of import?
> Thanks in advance!
> jg|||If the original appliation is pre-VFP, then it's probably not normalized, so
you'll want to re-think the database model as well. I've seen legacy -> SQL
Server ports where this was not done.
"Cindy Winegarden" <cindy_winegarden@.msn.com> wrote in message
news:eFWvPxZLGHA.2904@.TK2MSFTNGP10.phx.gbl...
> Hi Johnny,
> Download and install the FoxPro and Visual FoxPro OLE DB data provider
> from msdn.microsoft.com/vfoxpro/downloads/updates.
> When you set up your import you'll want to determine whether you have a
> database container (a DBC file is present in the directory where the DBFs
> are) or free tables. With a DBC you connect to the DBC and with free
> tables you just connect to the directory they are in.
> After that it's just like importing any other OLE DB compliant data.
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
> "johnnyG" <johnnyG@.discussions.microsoft.com> wrote in message
> news:CBE76BA7-C433-4EDD-8C73-123690CAA92E@.microsoft.com...
>|||Thanks guys...I downloaded the data provider and found the DBC and managed t
o
successfully import the tables. I'm reviewing the warnings now (some date
data type precision issues), but overall I'm underway. Thanks Cindy.
And JT thanks for the sound advice also...
johnnG
"johnnyG" wrote:

> Greetings,
> Any help/tips appreciated. I'm charged with importing a legacy FoxPro
> database appliction into SQL 2000 and building a new .NET front-end for it
.
> I've imported .mdb, Excel, text files, other MS SQL data, etc in the past,
> but the FoxPro stuff is rather different. I see the .dbf files (which I'm
> guessing will import as tables), but when I try to import, the DSN import
box
> comes up. I try 'Visual FoxPro drivers' but they're not installed. A litt
le
> research shows MS stopped bundling FoxPro drivers in MDAC 2.6.
> Does anyone have experience with this type of import?
> Thanks in advance!
> jg|||Hi Johnny,
In case you haven't figured it out yet, FoxPro Date data types (a date only,
no time) can be "empty" as well as null or having a valid date. SQL Server
doesn't know how to handle these and substitutes 12/31/1899 or 1/1/1900. The
same goes with DateTime data types.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"johnnyG" <johnnyG@.discussions.microsoft.com> wrote in message
news:FFFBC100-6D09-41CC-BB40-0452495014D2@.microsoft.com...
> Thanks guys...I downloaded the data provider and found the DBC and managed
> to
> successfully import the tables. I'm reviewing the warnings now (some date
> data type precision issues), but overall I'm underway. Thanks Cindy.
> And JT thanks for the sound advice also...sql

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.
Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into sql server 2000 relational tables. The database has simple recovery model.

The total size of data to be load is about 1gb (each file)

source:

Multiple source files with different layout.

Destination:

3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3 column unique index (alternate key) and a detail table that has a DETAIL_ID (IDENTITY) primary key and a foreign key ID to the parent table.

So, for each source file, I need to convert some columns to decimal and separate the data into parent and detail tables.

Here is one way to do this:

For each source file

* use a thread for each file with Microsoft Visual Studio .NET 2003

* run insert query with SP's por each record, and join parent table with details.

* process next record

My app works fine in XP and SQL express, When I run the queries the CPU utilization is consitantly around 90%. This is my development environment.

My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.

When I run the queries the CPU utilization is consitantly around 3%.

I have 2 Questions:

1- Is there a better way to do this?

2- Why XP has utilization around 90%, and Win2003 3%?

Speed is the primary concern.

Thank you for any suggestions.

Using SPs calls is not the fastest way. The quickest way is to generate files including the identity values and then bcp/.bulk insert the data into SQL. You could use SSIS to do this as well.

The reason you should generate you ID values outside of the database is so that when spliting your input file into your tables you can just insert the data straight into the tables. You can let SQL generate the PK identity values of the detail tables.

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp
1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a
3
> column unique index (alternate key) and a detail table that has a DETAIL_I
D
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition
Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--