3

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?

Improve this question
4
  • What version of SQL Server? How many tables? Do you have a local version of SQL Server that you can import into then generate scripts from? (Addins such as SSMS Tools can generate these statements from SQL Server) Commented Sep 3, 2010 at 21:45
  • 2
    I didn't know there was anyone that would be happy to use VBA. :-) Commented Sep 3, 2010 at 23:37
  • If you want help from people with extensive knowledge of Access, you would be wise not to make snarky remarks about it. Commented Sep 4, 2010 at 19:34
  • It's a "joke" based on premises that are likely to cause people of good will to question your sincerity. And the "I was just joking" excuse is pretty juvenile, in any case, even it it was intended as a "joke". Commented Sep 5, 2010 at 21:59

3 Answers 3

Reset to default
3

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.

alt text alt text

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

that's perfect. i forgot about the ability to link to an MS Access database from SQL Server.
well it looks like it would've worked except the SQL server I'm running is 64 bit and it seems that there are problems with connecting from 64 bit SQL Server to 32 bit Access using a linked server. Oh well guess I'm back to screwing around with Excel formulas.
@jkohlhepp: I tried that myself, as well! Consider spinning up a new VM with a 32 bit Windows. It'll be useful for other scenarios as well. Good luck!
If you installed the 64-bit Jet/ACE wouldn't 64-bit SQL Server be able to use that?
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.

Improve this answer

4 Comments

So you used SSMS, even though, in your question, you said that you could not?
If you did what @p. campbell suggested, then you should award him the answer! He may not have gotten every detail exact (you imported instead of DTSed) but give him a break: he guided you accurately.
i'm with @emtucifor. all but the way to import into sql server was in @p.campbell's answer.
Okay answer awarded to @p.campbell, as I did use parts of it. But the piece of using the SSMS Import wasn't mentioned. And in my question I said I couldn't use SSMS to talk to the production database. But to use it against my dev database as a stepping stone for creating the INSERT statements was a possibility. But that probably wasn't clear in the question which is my bad.
1

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.
Improve this answer

1 Comment

This is the approach I started to take, but it became a PITA because the data has annoying characters in it like newlines, single quotes, etc. The Excel formulas to get rid of all that junk was becoming hard to manage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.