Need to export one table to another database in a different server/db

use the import/export wizard on ssms.

Can the import export wizard do just one table ?

Yes, it can.

What I have done in a similar situation is: create a temp database, select * into temp.temptable, backup the db, restore it on the target instance and use it there to copy data.

Also a linked server might be an option.

This is the fastest and simplest way! Of course, assuming you have a LinkedServer already in place.

I've done this hundreds of times with billion rows type of tables and had no issues.

You may create some temporary locks while reading the data but that will vary depending of each system and your app design.

You do not need to have a linked server to use the import / export wizard.

And, for a larger table, I would not be too sure that this is the fastest method.

As an example, I had to copy a 64 GB table from one server to another on Monday. I started the package Monday morning, it finally completed on Wednesday night.

BCP out, using native mode, copying and bcp in took less than 2 hours. The bulk of the time was spent copying the file.

All my servers at work have linkedserver so I don't worry about that but I think you're right for import export wizard, you may need to oracle or sql driver and connections, etc.

But in my experience , import and export wizard is as fast as bcp. Like I said, I've copied billion records this way and tested with bcp and it was as fast as bcp.

Bcp of course can be faster and even more flexible on more situations but I wouldn't call it a "simple" way. You will have to play around with it a bit to master all switches and options. The wizard is just a GUI, and based on the OP question it seems it's more suitable and simple on his particular case.

+1000

sql-lover (3/1/2015)

That's never been my experience but that's not to say it didn't happen. Different environments work different ways. How long did that bad boy take to complete and how wide were the rows?

The wizard has progress indicators and the like so bcp, avoiding such, would be slightly faster. In this case faster might not be better. The OP seems to be new to the concepts so the wizard would be a good way to get the feet wet.

If the databases are on the same server then the wizard will copy the structure and the data. Even on different servers you can use linked servers with the wizard (IIRC).

Wait a sec! If the servers are linked can't you do a SELECT INTO?

The servers do not need to be linked to run the import / export wizard.

The things that typically make the wizard slow are:

1. Network configuration / bandwidth.

As an example, dev and stage are in one physical location, production is in another physical location in my environment. The pipe is fairly small. Unless it's a small table, this is painful in my set up.

2. Running it from your local machine from one server to another. This extra hop makes difference.

The wizard is actually SSIS. You can save this as a package, and modify it using Visual Studio.

I have never seen the wizard perform faster than BCP. There are a couple things to make BCP run faster.

If you are going SQL Server to SQL server, use native mode, not character mode.

Set the batch size to a smaller number, like 100. It may not be faster, but it sure looks like it's flying!

You can write the file to a network share using the UNC. You definitely want to set a small batch size if you do this.

The syntax for OUT

BCP YourFROMDatabaseName.dbo.YourTableName OUT YourTableName.txt /m 100 /b 100 /e YourTableName.err /n /S YourServerName /U <sysadmin level user> /P <password>

The syntax for IN

BCP YourFROMDatabaseName.dbo.YourTableName IN YourTableName.txt /m 100 /b 100 /e YourTableName.err /n /S YourServerName /U <sysadmin level user> /P <password> /E

Switches

/m indicates the max number of errors

/b is the batch size

/e is the error file

/n is native mode

/S is the servwer name

/U is the user

/P is the users password

/E is preserve any identity columns. If no identity columns are present, it ignore this.

I would not do SELECT INTO TableName across a liked server is this table is of any size. This method probably has the most overhead.

Yes, I agree. The wizard is easier.

But if this is going to be something done on a regular basis, the OP can use this as a learning experience and make decisions on what works best in their environment.

Michael L John (3/4/2015)

Ouch.

I had assumed that this was a one time thing where the table did not exist on the target. The wizard would have taken care of that aspect. Yep, you are going to have to learn bcp sometime anyway.

Another way you can do it in SQL Server 2014:

SSMS:

Right click on the database, select Tasks, select Generate Scripts.

Click 'Select specific database objects'.

Expand the Tables tree and click the box of the table in question.

Next.

Click the Advanced button.

You'll get a screen with lots of options. The critical ones are the last one of the General section that allows you to script out data in addition to just the schema, you'll want 'Schema and data'. In the Table/View Options section you want to turn on the options for Script Indexes, Script Primary Keys, Script Triggers (perhaps) and Script Unique Keys. You might want to turn off the Script USE DATABASE option.

The output has the DDL for building the table along with all defaults, indexes, etc. And it also has the DML for inserting all of the data. You definitely should review the output before running it in another database.

I do this all the time to make design changes to tables that already have data in them in SQL Server 2014, no guarantee that this will work with previous versions. I usually also have to use the 'Generate Script for Dependent Objects' option, it works really well when tuning a design during development.

Of course, if it's a big table, I'd use other methods, but it works fine for 600ish row tables.