pzozulka
asked on
SQL: Export data to another database
I need to extract some data from a production database into my own test developer database on my laptop. I only need to extract maybe the top 30 rows from a few tables, and don't need the entire table or database because this is only for testing purposes.
I have already done "Script table as" feature so that I can recreate the table and all its columns on my end.
Now I need to extract the data (top 30 rows) from the table. One other GOTCHA: the extracted data needs to be saved to a script or file, and then emailed to my laptop where at a later time I can run the script to recreate the data on my end.
I have already done "Script table as" feature so that I can recreate the table and all its columns on my end.
Now I need to extract the data (top 30 rows) from the table. One other GOTCHA: the extracted data needs to be saved to a script or file, and then emailed to my laptop where at a later time I can run the script to recreate the data on my end.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
set up your laptop instance as a linked server on the production server assuming you have the permissions.
ASKER
I do not have the ability to do so. As mentioned in the original post, the time of exporting will be days apart from importing to my laptop.
apologies...somehow i skipped over that last paragraph. Depending on how many tables you're talking about, I assume many, your best option might be to create a copy of the database on the production server with the subset of data you need via the option i presented or third party software like RedGate SQL Data Generator. Keep in mind that just taking the top 30 rows might not work due to referential integrity. Once you have the subset DB you can just back it up and email the .bak file.
ASKER
I do not have admin rights to the production database, so I don't think I can create a copy of the DB. Also, the DB belongs to a client so I don't think they would like me making an entire copy of their DB.
Any other suggestions? I was hoping there was another "Script As" option I wasn't seeing.
Thanks for your help.
Any other suggestions? I was hoping there was another "Script As" option I wasn't seeing.
Thanks for your help.
Honestly, I don't see an automated way to do this especially considering table relationships. For instance if you have an Order table and you take the top 30 then you need the OrderItem records that correspond, NOT just the top 30 rows. Although I can conceive of a program that crawls through a DB creating what you are talking about, it would be a monster to write generically. You would have to pull records from a table then analyze the constraints to move to the next table and so on.
ASKER
Hmmm....you bring up a really good point. OK, let's take a step back. Forget the top 30 rows thing. What about extracting data from entire tables. The only one I foresee having problems with is similar to the Order table. But other tables, are more of settings tables that only have maybe 50 rows all together and never change.
In total I need to extract about 10 tables. I would say 8 of them are static tables that never change. What would be a good way to extracting ALL data from those 8 tables?
In total I need to extract about 10 tables. I would say 8 of them are static tables that never change. What would be a good way to extracting ALL data from those 8 tables?
ASKER