copy table to anoyher DB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

copy table to anoyher DB

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-04 : 03:32:38

Hi all I need to copy all my SP and tables...
I scripted and got my SP..
Only table structure i got ...
How to get table values ...

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-04 : 04:56:27

how to copy one db to other

Kristen
Test

22859 Posts

Posted - 2010-09-04 : 05:32:01

"How to get table values ..."

If you were using SQL 2008 there is inbuilt script-data as well as script-DDL. There are 3rd party scripts available that will do the same thing - e.g. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Tool%20to%20script%20database%20records

Or Backup the database and Restore as the Target database

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-04 : 05:38:45

i am using sql 2005 and i need some tables only

Kristen
Test

22859 Posts

Posted - 2010-09-04 : 06:04:49

Follow the linked provided then

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-04 : 06:25:10

I got some ideas

SELECT * INTO NewTable FROM existingdb.dbo.existingtable;

I think this will help for users....

Kristen
Test

22859 Posts

Posted - 2010-09-04 : 07:05:17

I thought the tables already existed and you needed to script the data for them?.

Your SELECT ... INTO will also create the table (so will give Error if target table already exists), and create any Identity column, but will NOT create PK, indexes, constraints etc.

If you just want to transfer data between databases you can use SSMS, or

INSERT INTO NewDB.dbo.NewTable SELECT * FROM existingdb.dbo.existingtable

but you will have to handle tables which have Identity Columns specially (which SSMS will take care of for you)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources