SQL Server copy table

How do i copy one table form one server to other in SQL Server 2005.

eg: from abc to xyz. and only thru querry. i know the way thru import.
aatishpatelAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
i missed the something

INSERT INTO TableName
SELECT* from  [LinkedServerName].[DatabaseName].[shemaName].TableName


the above one will work when you are in the Destination db..

otherwise


INSERT INTO  [ServerName].[DatabaseName].[shemaName].TableName
SELECT* from  [LinkedServerName].[DatabaseName].[shemaName].TableName
0
 
ezraaCommented:
You can use this to create a new table and copy all items from the old table:

select *
into newtable
from oldtable
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
if you already setuup a linked server, use

INSERT INTO TableName
SELECT [LinkedServerName].[DatabaseName].[shemaName].TableName
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
d1rtyw0rmCommented:
If the 2 database are not on the same server

You have to create a linked server to be able to access the databases on another server.  Here's a link that may be able to help you get started with linked servers:

http://www.databasejournal.com/features/mssql/article.php/3085211

Then you can use the following method as if the 2 database were on the same server

----------------------------------------------------------------------------------------------------------------------------

If the 2 database are on the same server

SELECT * INTO xyz.dbo.YourTableTemp FROM abc.dbo.YourTable

Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.

If you want to transfer all the objects from one database to another, open Sql Server Management Studio > Right click on your database > All Tasks > Generate SQL Scripts. Then run these scripts against the new database.

Transfer both schema and data

To copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1. This tool works for both SQL 2000 and SQL 2005 and generates a single SQL script file which can be used to recreate a database (both schema and data).
0
 
aatishpatelAuthor Commented:
no that does not work
0
 
d1rtyw0rmCommented:
yes it is
0
 
aatishpatelAuthor Commented:
i have SELECT *
INTO IHSMain.dbo.tblTest
FROM P001.dbo.preference_list

that does but does not import primary keys
0
 
d1rtyw0rmCommented:
Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.

If you want to transfer all the objects from one database to another, open Sql Server Management Studio > Right click on your database > All Tasks > Generate SQL Scripts. Then run these scripts against the new database.

Transfer both schema and data

To copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1. This tool works for both SQL 2000 and SQL 2005 and generates a single SQL script file which can be used to recreate a database (both schema and data).
0
 
aatishpatelAuthor Commented:
thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.