Copy ODBC DB to SQL SRVR??

[simetra]

Hi
What's the best way to do something like this:
I have a database that I can access via ODBC, and want to copy all tables and all the data into SQL Server. DTS fails on many of the tables - I get "error in caching".
I was able to write a VB app that cycles through all tables in the ODBC database, and builds a copy of them in SQL (using ADO and DAO). So, the empty tables are there, but am not sure how to import the data.
I had to change the names of the tables slightly when I built them in SQL Server. Also, I had to make all fields nullable to prevent errors of nulls going into "no-nulls" fields.
Now, I would like to build a Stored Procedure type of thing to populate these tables... But am not sure how to go about this.
There are several hundred tables, so I'd like to automate it as much as possible... say for each table, put the value of each field into the same-named field in the SQL table.
Any suggestions?
Thank You

 

[Snowcrash]

SQL Server offers the ability to import tables and data via the Import Data Wizard. It is possible to select the source to import from and the tables to import into for each table. It is also possible to change the datatype of the imported fields along with a nunmber of other field constraints which importing.

I would recommend having alook at this wizard before trying to write some code yourself. To access the wizard, go to the Enterprise Manager and right click on the database. From the popup menu choose All Tasks/Import Data and follow the wizard instructions.

Barb

 

[simetra]

Hi
Thanks for the reply. I do know how to run the DTS wizards. Unfortunately, I haven't found a way to have it loop through each field, in each table, converting the data types. Yes, I know how to edit the SQL, and/or use the "transform" button, but there are hundreds of tables, and I don't really want to do this for each individual table.
However, I'm thinking there's got to be a way to do this, possibly by building a stored procedure and executing it in a DTS task. One thing I'm thinking is that I could build a stored procedure which takes the source table name as an argument, builds the destination table, and builds all the fields in the format I want. Then, have another stored procedure that takes the same table name as an argument and does the actual import.
Thanks again for your help!

 

[softwareguy]

Have you looked at any third party data migration tools like Data Junction?

http://www.datajunction.com.

There's a pretty good learning curce but great for moving & transforming data.