Which is the fastest way to move a table from one DB to another ?

Question

Which is the fastest way to move a table from one DB to another ?

d viz

Ten Centuries

Points: 1092
More actions
September 11, 2009 at 5:32 am

#138006

Hello everybody,
i have the task of moving 5 tables (about a million rows each) from one DB X to a new one (that doesn't exist yet) one the same instance.
Disk free space is not an issue, the recovery model is set to simple and the tables have identity columns and timestamps
We are using SQL Server 2005 express.
Although this is more of an academic question since I have no limit as to server time or disk space, I would like to hear any thoughts or ideas from the community.
I was thinking to do this:
1. Manually create the Database (call it something like X_Archive...)
2. Use SELECT INTO from the old DB to the new DB thus creating the tables
3. DROP the old tables
Thank you
PS. I am a developer (competent in T-SQL) and not a DBA, but these days a lot of us do kinda both....

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]Why don't you use SQL Server Integration Services - SSIS where you can Import/Export Tables from one DB to another.

Let us know on this.

Mahesh[/font]

MH-09-AM-8694

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 2

SSIS or Import/Export wizard is good idea

othere options are:

1) insert/select (but not entire table at once, you should divide it into smaller pieces)

2) use bulk copy to export to file if you need to do this offline

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

SELECT *

INTO NewDb.NewSchema.NewTable

FROM Olddb.OldSchema.OldTable

Then, script the indexes and constraints and run those.

I'm not near an SQL Server right now and I forget if Identity columns will remain as Identity columns or not. I know they do on the same DB and I know they don't if going across a linked server. I just don't remember if they do or not on a different DB on the same instance.

BCP is certainly another option but you must create the new table first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

d viz Ten Centuries Points: 1092 More actions · Answer 4

Thanx everyone,

that is exactly what I did (select into.....)

The identity columns are preserved on the same instance...

I have never used SSIS - normally for tasks like those I feel more comfortable writing a quick and dirty VB or C program...

Paul White SSC Guru Points: 150468 More actions · Answer 5

For next time, http://sqlserver-qa.net/blogs/tools/archive/2007/04/23/use-import-export-wizard-with-sqlexpress-edition.aspx

Haven't looked into it myself, but I thought I'd post the link so you could choose whether to.

🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi