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?
soozhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0
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.
0
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...
0
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:
http://msdn.microsoft.com/en-us/library/ms162153.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.