Home / server / Questions / 268414
In Process
ferdyh
Asked: 2011-05-11 00:23:28 +0800 CST

Microsoft SQL alternative for Oracle's Imp and Exp command [duplicate]

  • 772

Possible Duplicate:
Microsoft SQL alternative for Oracle's Imp and Exp command

I'm looking a command line tool or SQL command to export/import a table or complete database. Much like the imp and exp commands of Oracle databases.

Any suggestions?

sql-server command-line-interface oracle export import

2 Answers

  1. If you're running MS SQL Server 2005 I think what you are looking for is Integration Services. This is a product which does many things including import and export. This replaces Data Transformation Services which is your main option on earlier versions.

    The easiest way of managing this is to use the Import and Export Wizard in SQL Server Management Studio, although there are other ways of invoking it. Find out more.

    • 1

  2. The SQL Server feature that is most like imp or exp is probably bcp. I believe that bcp.exe ships with the "standard" SQL tools. SSIS server objects do not ship with the standard tools and you may run into licensing issues if you are not running your app on the SQL Server.

    BCP is a utility that allows data to be exported from a table to a file or imported from a file to a table. BCP has been shipping with SQL Server for something like 20 years, and it is very primitive. It only understands getting data from tables to files or from files to tables. It does not handle creation tables or indexes. It does not handle parent-child relationships. If you need to export/import many tables, you will have to do them one at a time. If you need to create objects on the destination, your best bet is to use the automated script creation features of SSMS to create scripts that will create your tables and indexes. You can then run those scripts on the destination.

    BCP uses the standard "fast load" interfaces, which are exposed via OLEDB. If program is written in .Net, you should be able to use those interfaces to code up your own transfers. This would be a better solution than shelling out because you don't have to worry about whether or not a third party utility is on the client machine.

    Another way to handle this would be with linked servers, but they tend to be slow and large transfers may require a lot of log file space.

    If you need to do this on a regular basis, you might want to look at snapshot replication. This is a server-side solution that requires some set up and configuration.

    • 0