We are running a Microsoft 2012 SQL Server and are looking at a way to dump a subset of data (based on queries) to .sql scripts we could then run on another server to pull that data back in. We have an environment where we have a production server along with a staging server used to setup new products that touch multiple tables. Once the products are tested out, we want to export only the data for a specific product (based on queries across these multiple tables) and then pull that specific data into the production environment.
We've looked at the Task - Generate script method, but it only generates the full table, not a subset of the table data.
We've also looked at the Import and Export Data tool, but it only exports to another database (which we can't use because our production and test servers are in different environments for security) or a flat text file (cvs), not a script file.
Any ideas would be appreciated!