Solved

Export data from a specific SQL server 2005 table

Posted on 2014-09-19
5
232 Views
Last Modified: 2014-09-24
Hi,
I have a few tables I created on a test SQL server 2005. I need to create that table on a production server now. I was able to do that using  CREATE TO command that sql generated for me. But I need the data in the table also to be exported and imported into my production version.
Is there a simple way to do this? I was hoping the CREATE TO statement would have had the data in it also, but it didn't

Thanks,
Nacht
0
Comment
Question by:nachtmsk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40332942
You could use SELECT ... INTO, as in
SELECT *
INTO newtable
FROM oldtable.

Or you could use the CREATE TO first, and then
INSERT INTO newtable
SELECT *
from OLDTABLE
0
 

Author Comment

by:nachtmsk
ID: 40332986
Will the statement above create a file for me that I can use on the second server to import data?
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40333007
There are two approaches. One is to link the two servers together and then use the fully qualified table name, i.e. server.database.schema.object

If you don't want to do that, you may want to create a backup of your table on the test server and restore it on your production server. Then you only need the database.schema.object reference.
0
 

Author Comment

by:nachtmsk
ID: 40333071
I can't link the servers together, that's not possible.
Sounds like the other option will work. I didn't realize I could create a backup of a table. I thought I could only backup the database as a whole.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40333091
1. Backup the Database on TestServer.
2. Restore the Database (using a different database name) on ProductionServer.
3. Copy the table structure and the contents of the table across using TestServer.schema.tablename and ProductionServer.schema.tablename names.
4. Drop the new database on ProductionServer.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do I subtract date and time within a same column in SQL 4 50
Help Required 2 46
Optimize the query 5 50
Change this SQL to get all nodes 3 37
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

735 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