Converting Select Results into Insert Script - SQL Server

Converting Select results into Insert script - SQL Server

SSMS Toolpack (which is FREE as in beer) has a variety of great features - including generating INSERT statements from tables.

Update: for SQL Server Management Studio 2012 (and newer), SSMS Toolpack is no longer free, but requires a modest licensing fee.

Generate insert script for selected records?

If you are using the SQL Management Studio, you can right click your DB name and select
Tasks > Import/Export data and follow the wizard.

one of the steps is called "Specify Table Copy or Query" where there is an option to write a query to specify the data to transfer, so you can simply specify the following query:

select * from [Table] where Fk_CompanyId = 1

What is the best way to auto-generate INSERT statements for a SQL Server table?

Microsoft should advertise this functionality of SSMS 2008. The feature you are looking for is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table.

This is a quick run through to generate the INSERT statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:

1. Right-click on the database and go to Tasks > Generate Scripts.
2. Select the tables (or objects) that you want to generate the script against.
3. Go to Set scripting options tab and click on the Advanced button.
4. In the General category, go to Type of data to script
5. There are 3 options: Schema Only, Data Only, and Schema and Data. Select the appropriate option and click on OK.
   

You will then get the CREATE TABLE statement and all of the INSERT statements for the data straight out of SSMS.

Exporting data In SQL Server as INSERT INTO

In SSMS in the Object Explorer, right click on the database, right-click and pick "Tasks" and then "Generate Scripts".

This will allow you to generate scripts for a single or all tables, and one of the options is "Script Data". If you set that to TRUE, the wizard will generate a script with INSERT INTO () statement for your data.

If using 2008 R2 or 2012 it is called something else, see screenshot below this one

2008 R2 or later eg 2012

Select "Types of Data to Script" which can be "Data Only", "Schema and Data" or "Schema Only" - the default).

And then there's a "SSMS Addin" Package on Codeplex (including source) which promises pretty much the same functionality and a few more (like quick find etc.)

How can I generate an INSERT script for an existing SQL Server table that includes all stored rows?

Yes, but you'll need to run it at the database level.

Right-click the database in SSMS, select "Tasks", "Generate Scripts...". As you work through, you'll get to a "Scripting Options" section. Click on "Advanced", and in the list that pops up, where it says "Types of data to script", you've got the option to select Data and/or Schema.

Tool to export result set from SQL to Insert statements?

take a look at the SSMS Tools Pack add in for SSMS which allows you to do just what you need.

Script to generate SQL INSERT statements from SELECT statement in LinqPad

You can use DumpAsInsert from your first suggestion.

Because DumpAsInsert uses the IEnumerable more than once, you have to instantiate the query into memory:

var sel = this.ExecuteQuery<Accounts>(@"Select * from Accounts Where Actid < 100").ToList();
sel.DumpAsInsert("Accountscopy", "Actid");

---

Related Topics