Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Export data from a specific SQL server 2005 table

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
nachtmsk
Asked:
nachtmsk
  • 3
  • 2
1 Solution
 
Phillip BurtonCommented:
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
 
nachtmskAuthor Commented:
Will the statement above create a file for me that I can use on the second server to import data?
0
 
Phillip BurtonCommented:
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
 
nachtmskAuthor Commented:
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
 
Phillip BurtonCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now