PDA

View Full Version : Extracting Tables in SQL Server 2000



NancyJ
Mar 30th, 2005, 02:53 PM
Is there anyway to extract individual tables from an SQL Server database so that they can be inserted into another sql server database else where

Basically I have a db running locally that I need to take 2 tables from and insert them into a live database but I cant find any way to do this

DrumrGrl35
Mar 30th, 2005, 04:42 PM
You can do this fairly easily in Enterprise Manager - select the Table and then Export the data. Generate a SQL script to create the table, run that script on the other server (provided you don't already have that table created), then copy the data to the new server and Import it.


Hope this helps...

mahesh_r_blr
Apr 12th, 2005, 12:44 PM
goto ent manager ..select the table u want to export ..wehn it asks for destination ..choose ur new destination server and table after conencting to it first..

this will copy the table with all columns to new destination...

Bodestone
Jun 8th, 2006, 12:53 PM
This one said it failed and didn't show up until I re-posted. That one was written better so blanking this one.

Bodestone
Jun 8th, 2006, 01:56 PM
I have been looking for a way to do this from a script so that file name and path can be dynamic as well as data format.

Text output from a job is fixed width only and you can't get rid of headers. Similarly DTS packages are not dynamic enough. I would need to create one for every export type and database which isn't practical.

The closest I have come so far is cursing through a query and preparing a string from the results which is then echod to a file using xp-cmdsahell.

For obvious reasons though, this is not a practical solution for large data sets being exported from an actively in use database.