Best way to export and import tables
Posted on 2014-07-28
I have a database with 50+ tables where i need to export about 10 tables that have various foriegn key assocations. The relationships between the tables are all restricted to the tables themselves.
I would like to write a script that can export these tables and then import them to a second database (deleting any previous data). There are other tables so i cannot just drop and recreate the database.
The two databases are not visible to each other so i will have to do it using some file transfer. Also because of the nature of the foriegn keys, and identity columns i understand that there will need to be some setings to allow identity insert etc.
Ideally i would like to use xml to transfer the data because of its readabilty. There is not so much data.
I can see a solution which uses two stored procedure where i pass in table name, and the xml filename.
One stored procedure for export and the other for import.
Then it is easy for me to maintain the import/export but just having two script files.