Import tables from Access in SQL2005 Express
Hi, I'm using SQL server for the first time (SQL 2005 Express and SQL Express Manager) and I want to import tables and data from my MS Access database.
My problem is: how can I import tables structure from Access? Perhaps do I have to create the XML file containing the table schema? Ok if this is the way, I can create it but then how can I use it to create the table in SQL Server 2005 Express?
After the table has been created, how can I import data? I looked at the BULK INSERT and BCP commands in the help file, which is better?
Thanks
Hi,
Export the data from MS Access into a Text file and then use BULK INSERT to insert it into SQL Express.
BULK Insert is just the Transact-SQL equivalent of BCP command, you can use either. You will need to write a format file that can be used to map the data in the text file created by Access database to the SQL Server database.
Regards,
Vikram
Sorry Vikram but you didn't reply to my primary question.
In my Access project I have about 60-80 tables containing about 100 fields each. My problem is: how can I transfer the structure of the tables? I don't want to write any format file or whatever else. Moreover the XML file (created by Access exporting the table structure) contains all the necessary information to recreate the same table in SQL server, how can I use it?
Thanks
Hi,
If you already have the tables created based on the XML file, then you can use the BULK INSERT read the text file which can be a Comma Separated text file and load the data into the Database.
You would need to programmatically read the XML File and have code that creates tables in the database based on the XML.
Regards,
Vikram
Vikram I have NOT the tables created in SQL server. How can I create them using the information of the XML file?
At the moment forget the problem of importing data in the tables, because I haven't any table.
