Easiest way to generate INSERT statements from MS Access data

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 script开发者_JAVA技巧s. 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?

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.

Consider using a mix of Access and Excel.

• View your Access table in datasheet view.
• Select all rows
• Paste into Excel
• Insert a new column before Column A.
• Build your INSERT statement in this cell.
• Insert a comma between each column (insert new column) and single quotes as needed
• Insert an end parenthesis
• Drag the INSERT statement, commas, and end parentheses downward, copying their values for each row in your table.
• ensure you set SET IDENTITY_INSERT MyTable ON before executing that script.

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.