Best way to export and import tables
Hello,
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.
Any ideas?
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.
Any ideas?
Walter Ritzel
I would say that scripts is a good alternative. But, when you mention that databases are not communicating with each other, you mean that are in 2 complete separate networks? Because otherwise, SSIS would solve this more easily.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the replies... to answer your questions... the two servers are on seperate networks so SSIS would not work.
What i have is a test database and a live database, and i want a reliable and repeatable way of transfering the contents of these ten tables. They represent configuration data for our system. So this is a repeatble task...
What i have is a test database and a live database, and i want a reliable and repeatable way of transfering the contents of these ten tables. They represent configuration data for our system. So this is a repeatble task...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.