Copy tables from one db to another

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

General SQL Server Forums

New to SQL Server Programming

Copy tables from one db to another

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2012-01-31 : 17:28:55

I'd like to copy a group of tables from one database to another. I'm using SQL Server 9.0.4060.

These tables have identity fields, primary keys, indexes, default values and bit fields.

This is the code I was going to use.
SELECT * INTO TargetDB.dbo.NewTable FROM existingdb.dbo.existingtable

What will I have to manually here to make sure both tables are exactly the same in all regards?

Many thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-31 : 17:40:03

I would actually use INSERT INTO/SELECT. So I would script the tables first, run that script on the second system, and then I'd run INSERT INTO/SELECT.

Things to scripts: triggers, constraints, indexes, all DDL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 19:51:36

Tara's approach would be way to go if you want exact copy as SELECT INTO wont copy constraints etc in main table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RichardSteele
Posting Yak Master

160 Posts

Posted - 2012-01-31 : 19:56:59

How then do I script the structure of the new table to reflect the old table so that everything is covered? Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 19:59:34

you can script it out by expanding database-> tablename in sql management studio right clicking on it and choosing option 'script table as create'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RichardSteele
Posting Yak Master

160 Posts

Posted - 2012-01-31 : 20:09:46

Then do I have to explicitly manually script each column name in the insert?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 20:12:39

quote:
Originally posted by RichardSteele

Then do I have to explicitly manually script each column name in the insert?

not each column

just script tables, it will include all the columns also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RichardSteele
Posting Yak Master

160 Posts

Posted - 2012-02-05 : 18:43:22

Then do I have to explicitly manually script each column name in the insert?

RichardSteele
Posting Yak Master

160 Posts

Posted - 2012-02-05 : 18:43:34

How then do I script the structure of the new table to reflect the old table so that everything is covered? Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 19:25:47

quote:
Originally posted by RichardSteele

How then do I script the structure of the new table to reflect the old table so that everything is covered? Thanks!

just script out old table
and just do find and replace to modify old name with new table name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources