Copy a table 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 a table from one DB to another

Author

Topic

joshymraj
Starting Member

5 Posts

Posted - 2007-05-24 : 21:10:46

Hi friends,
Can anyone help me with a way to copy a table from one Db to another including the contents of the table.

Thank You

joshymraj

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-24 : 21:18:57

There are many ways to do it. If you are new to this, DTS is probably the easiest way. It has a wizard to assists you.

Take a look at DTS in Books On Line.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-25 : 03:31:50

or,
1. Generate a script for the table and its associateds constraints, indexes etc.
2. Run the script in destination database
3. Copy the data using INSERT INTO...SELECT statement

Insert into destdb.dbo.desttable
Select * from sourcedb.dbo.sourcetable

Note: I assume both DBs are on same server.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-05-25 : 03:37:32

If you just want the basic database structure and the data, and the two databases are on the same server then:

SELECT *
INTO TargetDatabase.dbo.MyNewTable
FROM SourceDatabase.dbo.MyOldTable

will do.

For a remote server which is LINKED you can do:

INTO RemoteServer.TargetDatabase.dbo.MyNewTable

instead.

But that (and DTS) won't create indexes, constraints, FKs, etc. If you want all that you will need to script the table, create it on the target database, and then get the data in using either DTS or INSERT .. SELECT ...

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-25 : 03:40:40

Kristen,

I don't believe you are suggesting SELECT...INTO syntax for copying table schema and data. I try to avoid that even for temp table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-05-25 : 04:08:03

It locks the Create Table thingie, which could be for a long time on a big table. Other than that I don't see much downside (for a one-off job).

OTOH I think it has huge "upside" for a novice in that they don't have to mess around with scripts, learning DTS, etc. But of course its only really of any use for databases on the same server.

One other issue is that it will use Default Collation - which will be a problem if different to the collation of the Source Table.

Anyway, I'm all ears: Why are you anti?

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-25 : 05:07:48

Most of the issues are already what you described (like locking of system tables, collation issues etc.)

Besides that, I don't think it will be a good advice for a novice as they get into habit of doing it without knowing pros and cons of the approach.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources