How to Move or Copy the Tables from One Database to Another Database ?

[Former Member]

HI,

Can any one help me on this, How i can move or copy the tables from one database to another database in SQL server 2005 by using SQL query. Hope can anyone provide me the useful and valuable response.

Thanks

Gopi

[Former Member]

Hi, i have done this with, here is the code,

Example, Table A at server1, Table B at server2::

insert into server2.dbo.TableB (field, field)

select server1.dbo.TableA (field, field)

The same for the query :

select a.field, b.field

from server1.dbo.tableone A

INNER JOIN server2.dbo.tabletwo b ON a.campo1=b.field1

Hope it's works.

Grettings.

[Former Member]

Hi,

check SAP KBA 1683385. This explains how to export a table from one DB and import it into anther one.

regards,

beate

[Former Member]

HI ,

The above mentioned SQL query is working fine, but i need to copy the entire tables from one database to another database, i don't want to mention the table name as given in the above SQL Query. The tables in the source database has to be append in destination database.

Regards

M.L.Gopinath

[Former Member]

HI All,

Can any one please help me on the above issue its pending for long long days.

Regards

M.L.Gopinath

[clas_hortien]

Hi,

can you please be more specific about

- why do ou want to do this

- is the target a SAP System as well

- do all tables from the source exists in the target

- is there any data in the target system

- if there are no tables in the target, do you need only the data or the metadata (indexes, primary keys etc.) as well

Best regards

Clas

[Former Member]

HI Ganesh,

I tried using the above SQL query but the table is copying in the Same database itself, can you please provide the screen shots that how i can copy the tables from one database to another database by using SQL Query.

And importantly in the above query we have to use the table name to extract the tables from one database, please help me that i need the tables in both the database.

Thanks

M.L.Gopinath

[ganesh_borase2]

Hi Gopi,

I have tested again

do one thing,

execute following SQL

1. Create one table name test in pubs datbase

2. execute the SQL

use AdventureWorks

SELECT * INTO AdventureWorks.dbo.test FROM pubs.dbo.test

this will copy the test table from database pubs to AdventureWorks

verify the same.

It will definitely work.

[ganesh_borase2]

Hi,

If you have a table in a database and you would like to copy the table to another database, use this query:

e.g.

SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers

Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.

Thanks

Ganesh

[Former Member]

HI,

I need the database in both the server, i just want to take the copy of the database, that too only tables and its contents, how i can do that by using SQL Query.

Regards

M.L.Gopinath

[William_Laverde]

Hello,

Maybe these links help you out

http://www.microsoft.com/downloads/en/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&dis...

http://www.suite101.com/content/how-to-copy-a-sql-database-a193532

Also, you can just detach the database make a copy and move it to the new server.