Easiest way to generate INSERT statements from MS Access data

Question

I have a bunch of data in MS Access. I want to create INSERT statements from the data with the purpose of running them in SQL Server. The table structure between Access and SQL Server is the same. I don't have the option of doing an export/import because I don't have direct access to the SQL Server. It is a web host's server and they only give you a stupid control panel to run scripts. Unfortunately I can't use SQL Server Management Studio against it, or any other tools.

What is the easiest way to generate SQL Server compatible INSERT statements from MS Access data?

Answer 1

Install a copy of SQL Server (perhaps Express) on a machine (your dev machine, a VM, whathaveyou). Ensure your .mdb can be read by this machine.

• Use SQL Server to create a Linked Server to your Access database.
• DTS/SSIS tables from Access to your local SQL Server.
• Export scripts + data from your local SQL Server. Right click your database, select Tasks-> Generate scripts.
• choose to script data.

This will ensure that your create statements are followed by the data.

Answer 2

It turns out I found a way that was easier than either of the suggested answers. I went to SQL Server Management Studio and right-clicked on the database, chose Import, and went through the wizard to import from an MS Access datasource. It was fairly painless and straightforward. Then I generated scripts as p. campbell suggested.