Easiest way to generate INSERT statements from MS Access data

views:

answers:

+1 Q:

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 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?

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.

p.campbell 2010-09-03 21:48:17

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.

jkohlhepp 2010-09-03 23:39:08

+2 A:

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

p.campbell 2010-09-03 21:53:23

that's perfect. i forgot about the ability to link to an MS Access database from SQL Server.

jkohlhepp 2010-09-03 23:36:10

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 2010-09-04 22:02:03

@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!

p.campbell 2010-09-04 23:22:36

If you installed the 64-bit Jet/ACE wouldn't 64-bit SQL Server be able to use that?

David-W-Fenton 2010-09-05 22:00:36

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.

jkohlhepp 2010-09-06 16:54:46

So you used SSMS, even though, in your question, you said that you could not?

Traingamer 2010-09-07 17:41:54

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.

Emtucifor 2010-09-07 18:15:04

i'm with @emtucifor. all but the way to import into sql server was in @p.campbell's answer.

DForck42 2010-09-07 18:38:14

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.

jkohlhepp 2010-09-07 20:05:54

@jkohlhepp: thanks!

p.campbell 2010-09-07 20:16:27

@p. campbell did suggest using a local SQL server instance...

Emtucifor 2010-09-07 21:33:12

ansaurus

tags:

views:

answers:

Easiest way to generate INSERT statements from MS Access data

related questions