Solved

Best way to export and import tables

Posted on 2014-07-28
4
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 40

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL trigger 5 32
MYSQL responding very slow 3 49
sql select total by week ending 3 24
SP result not being displayed 5 40
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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 …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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