Importing SQL tables from one database to another

[Guest_imported]

I'm wondering if there is a way to copy tables from one sql database to another without recreating them. For example, copying a table from a test database to the live database. Please answer if you have any ideas...

Thanks!

 

[smnetto]

Hi, the SQL Import/Export Data Module (DTS) Data Transformation Services do this very well. Its included in SQL Server.

Sergio M. Netto.

 

[tlbroadbent]

Alternatives to DTS.

1) Backup the database on ServerA and Restore it on ServerB.

2) Detach the database on ServerA. Copy database files (generally MDF, LDF and sometimes NDF) to ServerB. Attach database files on ServerB. Reattach files on ServerA.

I prefer either method over DTS for transferring complete databases. These methods are faster and simpler. See SQL BOL for details of these methods. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

>>....if there is a way to copy tables...
>>....For example, copying "a table"....

I wonder does <e_moore> want to copy: (a) only tables, not other objects, and (b) only one or some tables, but not all.

If (a) or (b), then DTS might be more suitable. The backup and detach strategies work really well, but will result in all objects being copied (isn't that right?). Plus, if the databases are on different servers, then there's the usual headache with screwed-up logins.

bp

 

[tlbroadbent]

I agree that DTS is a better choice for copying tables only. I misread the question. If only copying the schema and not the data, I usually script the table and then run then script in the other database rather than use DTS. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[bperry]

Actually, when doing schema only, the scripting route is generally what I do too.


Have you worked in an Oracle environ, where I gather that *everything* is done through scripting?

bp

 

[tlbroadbent]

Fortunately, I've never worked in Oracle! :-D Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[Guest_imported]

I have a related question...

Say i have a live db and a development db. I add table fields and write new stored procedures to the development one and the same time my users add new data to the live db. So when I am getting ready to roll out the development db (it is backward compatible to the live db), what is the best way to combine the new db structure and objects from the development db and the data from the live db?

thanks!!!