Speed up export from SQL server 2008 R2 to Microsoft Access

0
votes

I need to export 700,000 records from an SQL Server 2008 R2 table to a Microsoft Access database in 2002-2003 format. I am using the SQL Server Import and Export Wizard. This is currently taking over 2.5 hours. Because this is all taking place on a high secure server I am limited in my choice of tools. I could export to a text file but that loses some of the formatting.

I need a copy of one table from the database in either Access or Excel with formatting preserved. Exporting to text/CSV is not available as some of the fields may have commas. Also I cannot use Excel as the target because 2008 R2 does not support mode that 64K rows

Are there any ways to speed this us?

1
sql-serverms-accesssql-server-2008-r2
Can you provide a little information on why you are doing this? It might help us avoid unsuitable suggestions. For example, if you simply need an offline copy of your data perhaps it would be quicker to address the formatting issue? - David Rushton
Not quite sure what you mean by "preserve formatting", but the Append query in Gustav's answer should preserve everything from the source table. - Andre

1 Answers

3
votes

Using Access it should be a snap:

Link the table via ODBC, create an empty table in Access as it should appear.

Then run an append query using the linked table as source, and writing the data to the local table. The query can also rename the fields (alias) and perform minor modifications as to your needs.

If you don't have Access (Office) 2016 installed, I believe a 30 day evaluation version is for download.