I often have to copy data from our production environment to our testing environment. They are in completely different environments where prod is behind a firewall that I have to VPN into so I cannot use linked servers. Submitting a work order to have the DBA's do this task often takes too long so I'm trying to find a systematic way to do this.
Creating a set of insert scripts based off of a select query seems to be the best and will do the trick. The reason this will work is that these are often one time copy scripts that are static/lookup tables that aren't too big.
For example if I had a table called "Cars" and it had a CarId and Description column, then a script would be:
select 'insert into Cars (CarId, [Description])
values (' + cast(CarId as varchar) + ', ''' + [Description] + ''')'
from Cars order by CarId
In my test database, I can simply blow out the table before I run the above insert scripts.
My prod sql user also has select access to all of the INFORMATION_SCHEMA tables, so I'm wondering if anyone has come across a systematic way to write the above query. Often times, the tables I need to copy have a lot of columns and the casting is different for datetime, varchar, etc...
EDIT: Oh, and I forgot to add that my prod user does not have permissions to execute stored procs or functions
