Copy table - 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

Express Edition and Compact Edition (2005)

Copy table

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-06 : 09:54:02

Hi,

I'm using management studio express with a 2005 express server.

Is there an easy way to copy a table from one database to another. In 2000 I would use DTS for this, is there an equivilent?

I know I can copy and paste between tables but the amount of rows is too much for the clipboard and crashes my machine.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-06 : 10:01:01

1. Generate script of the table in source database
2. Run the script in destination database to create table
3. Insert into DestDb..DestTable(col1, col2,...)
Select Col1, col2,... from SourceDB..SourceTable

Above method will work only if both databases are on same machines.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-06 : 10:29:53

Thanks,

1 and 2 no problem, but what do you mean by 3 ?

If I have two fields for example Name and Address

INSERT INTO dbA.TableA(name,address) VALUES (SELECT name,address FROM dbB.TableB)

Will that do all the rows?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-06 : 10:42:11

Yes, but you got syntax wrong:

INSERT INTO dbA..TableA(name,address) ~~VALUES (~~SELECT name,address FROM dbB..TableB)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources