Solved

Export data from a specific SQL server 2005 table

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
A short film showing how OnPage and Connectwise integration works.

932 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

10 Experts available now in Live!

Get 1:1 Help Now