Web Development

Ask something
Discussion Locked

Copy tables in SQL2000 using sp?

0 helpful
5 replies

by ohjani

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
are there any ways to copy tables (including structure and data) from one server to another besides using Replication or from the Enterprise Manager Console? this process is only going to be used once or twice and the tables involved will not exceed 10. speed is not an issue.

5 replies

by miket

A couple of options:
1) Use Ent. Mgr. to script the table structures and select the option to drop the objects. Then just append your "Insert Into TargetTable Select From SourceTable" query(s) at the bottom of the script generated by EM.

2) Usethe Data Transformation Services. It's very simple to use. You can have it save the package and run it later manually, or schedule it to run at a certain time(s).

Good luck,
Mike T.

by glen_mcleod

You could also use linked servers with the standard SQL syntax "Insert Into ... Select ... From", or "Select Into ..."

Example: Svr1 and Svr2 are linked, and you want to copy data from Svr1.MyDB1.MyTable1 to an existing table on Svr2.

Insert Into Svr2.MyDB2.dbo.MyTable2 Select * From Svr1.MyDB1.dbo.MyTable1

If the table doesn't exist, but you want to create it, use the Select Into syntax.

Select * From Svr1.MyDB1.dbo.MyTable1 Into Svr2.MyDB2.dbo.MyTable2

You don't need to specifythe 4 part name for the server you're actually connected to, but this way it'll work from either machine.

Linked servers are not hard to set up, use Enterprise Manager, and it's just a few clicks.

Glen

This discussion is closed to new replies.