How can i copy data base tables with data from one data base to another database?

How can i copy data base tables开发者_StackOverflow中文版 with data from one data base to another database?

---

Assuming you have SQL Management Studio you can use the Export Wizard.

RMC your database in the Object Explorer then choose Tasks -> Export Data.

You can then choose another database to export to or even export to a file eg cvs file.

---

Can you give some more information?

Do you want to do it in an Application or manual?

In case you want to do it manual the SQL management studio option is a good one. Or you can use scripts like this http://www.sqlservercentral.com/scripts/Miscellaneous/31447/ that turn your table data into insert scripts.

In C# you can use SqlBulkCopy.

---

If you have both databases on the same instanced, then you can just... copy it:

insert into DatabaseName1.SchemaName.TableName (Column1, Column2,...)
select Column1, Column2,...
from DatabaseName2.SchemaName.TableName

---

In the case that the databases are on the same instance and you want to create a new table you can use:

select * into table_to_copy
from DatabaseName2.SchemaName.table_to_copy

Note: This copy has no primary key nor other constraints, but these can be added afterwards. Only a possible identity property is copied.

If your database is on a different instance and/or even a different DBMS type, you can use linked servers, but then there can be some restrictions. Some column types will no be copied. I found problems when I wanted to copy ORACLE CLOB columns.