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
Solved

Export data from a specific SQL server 2005 table

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

789 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