Create an insert script from select results

Question

Using SQL Server Management Studio is there a way I can select one or more rows in the grid of select results and have SQL Server Mangement Studio generate one or more insert statements (one for each row selected) which would insert that data into a table with the same schema?

Edit: I know how to create one manually, but I was hoping there would be something that would create it automatically for me. If you are familiar with Toad there is a way to have Toad generate inserts based on data in the results pane and I was hoping SSMS had an equivalant function.

Solution 1

Try to save the query result into a disposable table.

For example:

SELECT * INTO disposable_customer_table FROM customer_table WHERE id IN (in range of something)

Then do a db -> Tasks -> Generate Scripts.

• Select specific database objects.
• Choose disposable_customer_table from the list of table names.
• Choose Save to file.
• Make sure to do an Advance setup and select "Data only" from the 'Types of data to script'.

Tweak the result file and rename the disposable_customer_table back to the original table name.

Clean it up and drop the disposable_customer_table.

Solution 2

 select 'insert into tableB values (', tableA.x ,',',tableA.y,',',tableA.z,')' from tableA

Solution 3

I think you have two options here:

1. Create your inserts manually. For instance:

   select Name, Surname,
     'insert into Person (Name,surname) values ('''+Name+''','''+Surname+')'
     from Person
   

   This gets you the results and, in the last column, the insert script for the row. You can then select and paste it in an Editor window.

2. Right click on the db -> Tasks -> Generate Scripts. Press then Advance and select "Data Only" (Default is Schema Only).

Comments

[greg]

what version of ssms are you using? I am using 2012 and I do not see the SCRIPT GRID RESULTS or SEARCH GRID RESULTS options.

[Shane Wealti]

I don't have it either. I think that functionality is provided by a plug-in. OP?

[tbt]

this is done by the SSMS Tools Pack, unfortunately the SMSS 2012 version is not free