Solved

Export data from a specific SQL server 2005 table

Posted on 2014-09-19
5
220 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
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Creating and Managing Databases with phpMyAdmin in cPanel.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

746 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

12 Experts available now in Live!

Get 1:1 Help Now