Converting Select results into Insert script - SQL Server [closed]

Here is another method, which may be easier than installing plugins or external tools in some situations:

  • Do a select [whatever you need]INTO temp.table_namefrom [... etc ...].
  • Right-click on the database in the Object Explorer => Tasks => Generate Scripts
  • Select temp.table_name in the "Choose Objects" screen, click Next.
  • In the "Specify how scripts should be saved" screen:
    • Click Advanced, find the "Types of data to Script" property, select "Data only", close the advanced properties.
    • Select "Save to new query window" (unless you have thousands of records).
  • Click Next, wait for the job to complete, observe the resulting INSERT statements appear in a new query window.
  • Use Find & Replace to change all [temp.table_name] to [your_table_name].
  • drop table [temp.table_name].

In SSMS:

  • Right click on the database > Tasks > Generate Scripts

  • Next

  • Select "Select specific database objects" and check the table you want scripted, Next

  • Click Advanced > in the list of options, scroll down to the bottom and look for the "Types of data to script" and change it to "Data Only" > OK

  • Select "Save to new query window" > Next > Next > Finish

All 180 rows now written as 180 insert statements!


Native method:

for example if you have table

Users(Id, name)

You can do this:

select 'insert into Table values(Id=' + Id + ', name=' + name + ')' from Users

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.