Solved

Best way to export and import tables

Posted on 2014-07-28
4
223 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 15

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 15

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now