Solved

Best way to export and import tables

Posted on 2014-07-28
4
233 Views
Last Modified: 2014-08-12
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?
0
Comment
Question by:soozh
  • 2
4 Comments
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 40224728
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
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 100 total points
ID: 40224787
"... 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
 

Author Comment

by:soozh
ID: 40225622
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
 
LVL 16

Accepted Solution

by:
Walter Ritzel earned 400 total points
ID: 40226443
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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query question 12 32
Negative isnull? 3 15
Requesting help with creating an SQL query to select similar records 4 15
How to structure query with count aggregate 4 18
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question