Solved

SQL Server copy table

Posted on 2009-05-19
9
368 Views
Last Modified: 2012-05-07
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.
0
Comment
Question by:aatishpatel
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 9

Expert Comment

by:ezraa
ID: 24423210
You can use this to create a new table and copy all items from the old table:

select *
into newtable
from oldtable
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24423215
if you already setuup a linked server, use

INSERT INTO TableName
SELECT [LinkedServerName].[DatabaseName].[shemaName].TableName
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24423232
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
 
LVL 2

Expert Comment

by:d1rtyw0rm
ID: 24423343
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:aatishpatel
ID: 24423371
no that does not work
0
 
LVL 2

Expert Comment

by:d1rtyw0rm
ID: 24423469
yes it is
0
 

Author Comment

by:aatishpatel
ID: 24423511
i have SELECT *
INTO IHSMain.dbo.tblTest
FROM P001.dbo.preference_list

that does but does not import primary keys
0
 
LVL 2

Expert Comment

by:d1rtyw0rm
ID: 24423526
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
 

Author Closing Comment

by:aatishpatel
ID: 31583057
thanx
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Backup skipping a few tables 7 28
Save sql query result in sql server 15 25
sql server query? 6 28
Numeric sequence in SQL 14 38
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now