move big table from one server to another

when you want to move one big table and create it as it is in another server, is import/export the best option.?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

25112Author Commented:
server a has a table, let's say 'a'  with 12m records in database abc. i need the table with a different name, let's say 'b'  in server b in database abc. there is already a table called 'a' in server b.

will import/export be the most efficient since a big number of records is involved. full mode is fine as we have enough disk space (log management)
AnujSQL Server DBACommented:
You can try SSIS package, which you can have control on setting like buffer size, Rows per batch, Commit size etc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
what task in ssis package? can the above options be set in "SQL Server Import and Export Wizard"
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

AnujSQL Server DBACommented:
No you cannot set this option is import export wizard, this is a simple ssis package. In SSIS use Data flow task.
You can go with one of the following:-
1. Create a link server and write a job that runs every10 seconds and pull 10k to 50k rows from the sources server to target server. Make a stored procedure for that.
2. bcp out and bulk insert it in the target server.
3. create your own SSIS package to handle the number of rows and other parameters.
4. Go with export\import. The SSIS package created will do it in one sought. Not preferable for large table or a table being used continuously.
5. If the table size is huge and occupies more than 50% of the database size, you can backup the database, restore it in the target server and then do batchwise insertion within loop taking 5k to 10k rows in every loop. You can also go for creating snapshot type replication.
SThayaTechnical MAnagerCommented:
you can use  Transfer SQL Server Objects Task in SSIS task .
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.