CODEWITHSUNDEEP
ssms
Shane Wealti
Shane Wealti

Reputation: 2352

Create an insert script from select results

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.

Upvotes: 14

Views: 30362

Answers (4)

fgpx78
fgpx78

Reputation: 1280

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).

Upvotes: 4

sakadas
sakadas

Reputation: 569

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.

Upvotes: 15

m.edmondson
m.edmondson

Reputation: 30922

Perform your query and right click on the blank area where the column headers meet the row number in the Results view.

You can then select Script Grid Results:

enter image description here

Upvotes: 0

dov.amir
dov.amir

Reputation: 11627

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

Upvotes: 6

Related Questions