COPY TABLE FROM ONE DATABASE TO ANOTHER USING SQL SERVER 2000

Question

Post reply

COPY TABLE FROM ONE DATABASE TO ANOTHER USING SQL SERVER 2000

jeff_astro27

SSC Veteran

Points: 251
More actions
November 21, 2007 at 2:11 am

#184005

COPY TABLE FROM ONE DATABASE TO ANOTHER USING SQL SERVER 2000
AND IT IS IN SCRIPT.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Giri Duddu SSCommitted Points: 1638 More actions · Answer 1

If you want to copy table including data you can try the below querry

select * into

otherwise if you want to copy ony the table structure try below querry

select top 0 * into

Giri Duddu SSCommitted Points: 1638 More actions · Answer 2

If you want to copy table including data you can try the below querry

select * into otherdbname..tblname from currentdbname..tblname

otherwise if you want to copy ony the table structure try below querry

select top 0 * into otherdbname..tblname from currentdbname..tblname

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 3

Chandra Sekhara Vyas Dhara

SSChampion

Points: 10207

November 21, 2007 at 6:46 am

#753082

You can use DTS to copy the table into other database

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 4

dhara is right you can use DTS for transferring tables but if you want to use a script there are some that can provide you what you want just like the following:

1. If you want to create a copy of the table and data in the same database you can use this script. (Attached below)

2. If you want to copy the table schema in another database and you have an access to the enterprise manager you can do this :

2.a Go to the table name

2.b Right click the table nae

2.c Paste in the Query Analyzer

2.d run the pasted table schema

2.1 If you do not have access in the EM and only want to copy the table schem you can also use the script below (just read instructions for limitations)

3. If you already copied the table in other database (the table do not have any data inside) just run this script (supply valid information)

INSERT INTO table_name_copy SELECT * FROM db_name.db_owner.table

"-=Still Learning=-"

Lester Policarpio

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 5

Hi,

The best and the fast solution is using DTS.

DTS tutorial

The best site for DTS and SSIS is http://www.sqldts.com/

http://www.verio.com/support/documents/view_article.cfm?doc_id=3846

Regards,

Ahmed

jeff_astro27 SSC Veteran Points: 251 More actions · Answer 6

thank for the code.. ^_^

I finished my module because of this.

:P:P :):)

James Horsley Hall of Fame Points: 3196 More actions · Answer 7

dgvsbabu (11/21/2007)
If you want to copy table including data you can try the below querry
select * into otherdbname..tblname from currentdbname..tblname
otherwise if you want to copy ony the table structure try below querry
select top 0 * into otherdbname..tblname from currentdbname..tblname

This will lose all the indexes etc

James Horsley
Workflow Consulting Limited

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 8

Thanks for the correction James like i said in my first post this scripts has limitations hmm just revised it to add the recreation of index can someone check this to see what other adjustments must be made? Again i just want to emphasized that this script is made out of curiosity and like previous posters here DTS can also do the trick just made a script like this to see if it is possible thanks!!

"-=Still Learning=-"

Lester Policarpio

karthik M One Orange Chip Points: 29139 More actions · Answer 9

I would prefer DTS rather than SqlCode.

Because DTS will copyy the entire structure as it is in the another database. But when you use code it will not carry indexes along with it. Just the plain copy of table would be moved to the destination database.

karthik

James Horsley Hall of Fame Points: 3196 More actions · Answer 10

This actually sounds like it might be a target for replication rather than DTS

James Horsley
Workflow Consulting Limited

Ron-279530 Ten Centuries Points: 1381 More actions · Answer 11

Lester,

Thanks this was helpful to me on a related matter...

Ron

Lester Policarpio (11/21/2007)
dhara is right you can use DTS for transferring tables but if you want to use a script there are some that can provide you what you want just like the following:
1. If you want to create a copy of the table and data in the same database you can use this script. (Attached below)
2. If you want to copy the table schema in another database and you have an access to the enterprise manager you can do this :
2.a Go to the table name
2.b Right click the table nae
2.c Paste in the Query Analyzer
2.d run the pasted table schema
2.1 If you do not have access in the EM and only want to copy the table schem you can also use the script below (just read instructions for limitations)
3. If you already copied the table in other database (the table do not have any data inside) just run this script (supply valid information)
INSERT INTO table_name_copy SELECT * FROM db_name.db_owner.table