How do I transfer data to/from another DBMS/format to SQL Server?

A. A. There are a variety of methods/tools :-

1. For MS-Access you can try the free Access upsizing wizards available from MS's website - check out the softlib area under These will convert from versions of Access to SQL 4.x, 6.x and 7.x.
Try and
for more info.

2. If you can unload the data from the foreign dbms into flat-file format - e.g. tab separated, comma-separated, fixed-format etc. then you can use the SQL BCP.EXE tool. This is a command-line program and is fully doc'd in the books-online. It is the fastest way of getting data into/out of SQL Server, but it only works via flat-files. The unload and load processes have to happen in series - meaning longer run times.

3. If you have an ODBC driver for the other format then there are several 3rd party tools you can use that offer transfer/migration functionality and are gui-based, but can also be automated and run from the command-line. These tools can be used to copy to/from ANY ODBC data sources - they don't have to be SQL Server at one end. Examples of these sorts of tools are :- (Data Junction) (SQL Mover) (InfoPump)

4. If you have SQL 7.0 then this comes with a tool called DTS that works in a similar manner to the above. It works with any Odbc or OLE-DB accessible data source. Again it doesn't have to be SQL 7.0 at one end. However if you weren't using it to migrate to/from SQL 7.0 you would have to check whether there were any licensing implications.

5. Specifically for the AS/400 there are a couple of tools that have been around for some time - offering real-time replication as well as transfer. (Data Mirror). DataMirror will bi-directionally replicate between AS/400, SQL Server, Oracle, Sybase and DB/2. (Symbiator)

For generic ODBC/OLE-DB access to the AS/400 also see

Whatever you use you still need a driver to connect to the AS/400 that works - here is one that is reported to work ok with SQL 6.5 and 7.0.
IBM's Client Access ODBC driver v3.1.3 SP48155

6. Oracle has a Migration Workbench utility for free download. Search for "Migration Workbench".

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.