What I need to do sounds easy but I have no clue as to how to do this. Here is what i'm trying to do. I have a JET database that I want to transfer to SQL 2000 because I need the extra speed. I have the tables already in SQL 2000 because I had a .sql script that made them. The problem is, how do I transfer the data from the JET database to the SQL database and have the data goto the correct tables that are empty on the SQL server that the script made? Right now I don't have the JET database, all I have are the .mdb files from the jet database. Any help would be great, otherwise I have to spend the next two months adding data in ASP pages to rebuild the database from scratch.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Jet to SQL database, Please Help
- Thread starter hydroron
- Start date
Could have used the upsizing tool to do that it would have transfered the data and the DDL on one batch. DTS on SQL Server is another option or if you are really tring to swet you can write a T-SQL using Bulk Insert AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"
NT/DB Admin
"May all those that come behind us, find us faithfull"
Just an update having recently been through this.
The upsizing wizard is good if you do not want to fully-define the new SQL database and all its constraints, but it is very temperamental - tip allocate a new empty database first and 'use an existing database' in the wizard. This ensures that the database obeys the defined default locations for the data and log files as the wizard seems to just use the data file location.
However, you should carefully check the results of the wizard, as it can forget to do 'minor' things like set primary keys and default values etc. It it does not always transfer the data, sometimes that is due to SQL being more fussy with the data, especially dates, other times it is a complete mystery. If you cannot get round it, just upsize the database definitions and then use DTS to copy the data.
DTS packages can be used to refresh the data (note that the transformations button allows you to delete the old rows being inserting new ones as the default is to append all the data!). You will find that any RI contraints get in the way of doing a global copy of all tables to SQL, so you will either have to temporarily lose them (not good) or stage the data transfer to avoid problems (e.g. cannot delete all rows from a table as it contains foreign keys used by another table in an RI relationship). Test the packages several times as you can get slightly different results depending on what steps are run when by DTS.
One last point, DBCC CHECKIDENT is invaluable for reseting your new 'autonumber' fields after playing with the new tables when you test inserts and deletes.
The upsizing wizard is good if you do not want to fully-define the new SQL database and all its constraints, but it is very temperamental - tip allocate a new empty database first and 'use an existing database' in the wizard. This ensures that the database obeys the defined default locations for the data and log files as the wizard seems to just use the data file location.
However, you should carefully check the results of the wizard, as it can forget to do 'minor' things like set primary keys and default values etc. It it does not always transfer the data, sometimes that is due to SQL being more fussy with the data, especially dates, other times it is a complete mystery. If you cannot get round it, just upsize the database definitions and then use DTS to copy the data.
DTS packages can be used to refresh the data (note that the transformations button allows you to delete the old rows being inserting new ones as the default is to append all the data!). You will find that any RI contraints get in the way of doing a global copy of all tables to SQL, so you will either have to temporarily lose them (not good) or stage the data transfer to avoid problems (e.g. cannot delete all rows from a table as it contains foreign keys used by another table in an RI relationship). Test the packages several times as you can get slightly different results depending on what steps are run when by DTS.
One last point, DBCC CHECKIDENT is invaluable for reseting your new 'autonumber' fields after playing with the new tables when you test inserts and deletes.
- Status
Similar threads
- Question
- Locked
- Question
