fastest way to copy one table in a db to other db

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

fastest way to copy one table in a db to other db

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-07-25 : 10:13:26

I have 2 databases DB1 and DB2

DB1 has a very large table with a million rows, lot of this data is updated everyday. For testing purposes I have to copy database from
TABLE1 for DB1 into TABLE2 of DB2. The table name is different in both as per new requirements.

So I use this:

----------STEP 1--------------- COPY DATA ----------
select * into DB2.DBO.Table1 from DB1.DBO.Table1

----------DELETE THE EXISTING TABLE IN DESTINATION DATABASE-------
drop table table Table2

-----------ADD THE PRIMARY KEY-------

Alter Table Table1
Add Constraint PK1 Primary Key (COL1, COL2, COL3)

---------------RENAME TO TABLE2--------------
exec sp_rename 'DB2.DBO.Table1' , Table2

-----------------ADD NEW COLUMNS ---------------
Alter Table Table2
Add Coulmn Col20 varchar(10) NULL

Is there a way to do this with bulk insert or BCP any faster way.
It takes 7 minutes for this.

Ashley Rhodes

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-25 : 10:41:58

It's worth trying a bcp out and in using native format.
It will mean that you don't have to drop the table but will mean that you need to drop the indexes.

It would also mean that the source table will be released after the bcp out which should be the fast bit of the operation.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-07-25 : 10:44:36

can u give any sample script for this

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 11:13:23

Check out books on line for the BCP utility. They have sample code and explanation of how the utility works.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-25 : 11:21:26

Tried with dts?

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-07-25 : 11:35:37

need to do this in a stored procedure

when i give bcp in db1.dbo.table1

i get error as I cannot use the db name or owner name
but i am not sure if that is why i will check it out

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 11:42:45

As Nigel mentioned you can use it via BCP. Check out the sample code in books online. post what you have tried and we can help you out.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-25 : 15:44:59

You can run dts package different ways in sp.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-25 : 19:33:58

master..xp_cmdshell 'bcp mydb..mytbl out c:\mytbl.txt -n -Smyserver\myinstance -T'
master..xp_cmdshell 'bcp mydb2..mytbl2 in c:\mytbl.txt -n -Smyserver\myinstance -T'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources