asked on

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

Phillip Burton

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

nachtmsk

ASKER

Will the statement above create a file for me that I can use on the second server to import data?

ASKER CERTIFIED SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nachtmsk

ASKER

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.

Phillip Burton

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.