Sql-server – Exporting table records to an INSERT script

scriptingsql-server-2008-r2

I'm setting up a test environment for development and need to export some static data from tables in Production. Ideally the exported product would take the form of a script with all the required INSERT statements. In doing so, I can reset the development environment quickly after each test.

Can anyone tell how to accomplish this using SQL Server 2008, SQL Management Studio, and/or Visual Studio 2010?

Note: None of the tables have any foreign keys, I have full permissions in the source database, and the number of rows per table is no more than a few dozen.

Best Answer

As well as using SSMS, you can use the commercial SSMS Tools Pack

I prefer it: more options, batching, cleverer all round.

Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit between 0 and 10 Mb. Larger values than the limit are then scripted as NULL. Insert statements for the data in result grids are scripted into a new temporary table for each grid. For example from 5 result grids insert statements for 5 temporary tables get created.

Related Question