Generate Insert Script for Selected Records

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

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.

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.

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.

Screen shot of Advanced Scripting Options

SQL Server, How to generate an `INSERT` statement for the rows in an existing table?

Here are the steps:-

  1. Right click on your database in SSMS
  2. Choose Tasks>Generate Scripts
  3. Select specific database objects - choose the table you want to
    script. Click next
  4. Select 'Save to new query window'. Click the 'Advanced button'
  5. Change 'Types of data to script' to 'Data only'.
  6. Click OK, Next, Next, Finish.

The query editor window should contain the script you need.

Generate SQL insert statements of some records in a table

I found a way myself using Excel.

  1. Make needed query including WHERE clause in SSMS
  2. Select all the result
  3. Copy with header
  4. Paste in Excel file here under in 4th row, 1st column
  5. Change in macro output path
  6. Change in cell table name
  7. Launch macro
    --> take the file generated and you have a copy of your data ready to be insert again

Related Topics

Leave a reply