You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Pushing tables from one database to another database

5 replies [Last post]
ashok_star2004's picture
User offline. Last seen 28 weeks 5 days ago. Offline
Joined: 02/13/2009
Posts: 45

Hi,
Im using SQLServer2005.I have some tables in 1 database CRM90 which are not present in another database HRMS9.I want to move those tables to HRMS9 from CRM90 which are not present in HRMS9.I want to do this by writing a procedure in SQL Server2005.Can anyone help me up please!!

Thanks in Advance,
Ashok K.

Ashar's picture
User offline. Last seen 4 years 34 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Pushing tables from one database to another database

I wonder why you require procedure. You can create the table structure in the target and use the Data ranformation services of SQL Server 2005 to move the data.

ashok_star2004's picture
User offline. Last seen 28 weeks 5 days ago. Offline
Joined: 02/13/2009
Posts: 45
Re: Pushing tables from one database to another database

Hi Ashar!!
There is an requirement that in the front end we select table list and when we click on Move button it should cal a procedure to move those tables.Thats why preferring for procedure.

Ashar's picture
User offline. Last seen 4 years 34 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Pushing tables from one database to another database

Ok. I'm not sure how to do that in procedure. may be if u have linked server, you can directly access CRM tables and insert data into it.

Grace8Ward's picture
User offline. Last seen 9 years 41 weeks ago. Offline
Joined: 01/05/2011
Posts: 1
Re: Pushing tables from one database to another database

How do I copy certain tables from one database onto another database (both on the same server)???I am need only Structure of the table... not the contents...

Hari_aindian's picture
User offline. Last seen 6 years 35 weeks ago. Offline
Joined: 08/02/2010
Posts: 84
Re: Pushing tables from one database to another database

I prefer creating a database link in DB2 connecting to DB1, Which can be private db link also. Just follow the usual DB link creation syntax

CREATE DATABASE LINK DB1.SAMPLE CONNECT TO HARI IDENTIFIED BY HARI USING 'DB1.SAMPLE';

Test the db link:

SELECT * FROM ;

If you want to create an empty table (structure only):

CREATE TABLE copy AS SELECT * FROM  WHERE 1 = 2;

Regards,
Hari.