• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Best way to export and import tables


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?
  • 2
2 Solutions
Walter RitzelSenior Software EngineerCommented:
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.
lcohanDatabase AnalystCommented:
"... i need to export about 10 tables that have various foriegn key assocations. "

Is this a repeatable tasks where the destination table is overwritten or just a incremental export/import?
Or maybe just a one timer?

Anyway depending on what you need to do I suggest to have a look at transactional replication as it may work very well in your case especially that looks like you don't have too much data.

Another option would be to do a full backup/restore of the DB on the destination server but as different name then take the tables from that DB and populate the target on that same server - much easier and faster if backups are small and don't need close to real time sync.
soozhAuthor Commented:
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...
Walter RitzelSenior Software EngineerCommented:
Ok, now I got it. What you need is a export script and a import/deployment script.
So, yes, you are on the right path with this idea.
My two cents on that:
- If your tables contain few lines, try to use the features on SQL Management Studio to export the table structure plus the data (maybe in insert statements format);
- Then, create a script for cleaning up  the content in the correct order (to respect the foreign keys);
- Next, create a script to run the insert statements created on first step in  the right order.

Since all this is windows, I suggest a small powershell script to coordinate these SQL scripts at the appropriate order.

Here is a link to MSDN that teaches you how to create a powershell to export the database structure. A few changes should result on what you need for export piece:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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