Generate insert script for selected records?

user_1395266 0
Viewed 157339

I have a table with the following data:

Pk_Id  ProductName           Fk_CompanyId       Price
------------------------------------------------------
1      AMX                   1                  10.00
2      ABC                   1                  11.00
3      APEX                  1                  12.00
4      AMX                   1                  10.00
5      ABC                   1                  11.00
6      APEX                  1                  12.00
7      AMX                   2                  10.00
8      ABC                   2                  11.00
9      APEX                  2                  12.00

I want to generate Insert script for migrating records whose Fk_CompanyId is 1.

There is an insert script option to generate script for all records but I want to filter some records to migrate to another database.

sql-server-2008 sql database
13 Answers

If possible use Visual Studio. The Microsoft SQL Server Data Tools (SSDT) bring a built in functionality for this since the March 2014 release:

  1. Open Visual Studio
  2. Open "View" → "SQL Server Object Explorer"
  3. Add a connection to your Server
  4. Expand the relevant database
  5. Expand the "Tables" folder
  6. Right click on relevant table
  7. Select "View Data" from context menu
  8. In the new window, viewing the data use the "Sort and filter dataset" functionality in the tool bar to apply your filter. Note that this functionality is limited and you can't write explicit SQL queries.
  9. After you have applied your filter and see only the data you want, click on "Script" or "Script to file" in the tool bar
  10. Voilà - Here you have your insert script for your filtered data

Note: Be careful, the "View Data" window is just like SSMS "Edit Top 200 Rows"- you can edit data right away

(Tested with Visual Studio 2015 with Microsoft SQL Server Data Tools (SSDT) Version 14.0.60812.0 and Microsoft SQL Server 2012)

user_9212550

With the DBeaver client (which supports SQL Server) you can do a SELECT query with the records that you want and then select the resulting rows, right click and Copy as SQL, and you'll have the INSERT statement in your clipboard:

enter image description here



HeidiSQL also supports connecting to SQL Server and exporting selected rows (you can filter the rows in the SQL query itself, or retrieve all rows and select them in the data grid):

enter image description here

user_10402450

Import data doesn't have insert but I found very simple solution.

  1. Create new table with same table structure of your selected existing table.
  2. Use statement "Insert into NewTable select * from ExistingTable where Fk_CompanyId = 1"
  3. Task -> Generate Script -> select your new table
user_127349140

If you are using MySql and MySql workbench. Here is a nice option.

  1. Write your select query and execute it
  2. You'll see the Export button
  3. List item
  4. Click on it and give a filename and in "Save as Type" you'll see "SQL Insert Statements"

This will give you insert statements

enter image description here

user_14157390

Use Navicat

  1. Filter your data in table or write your query

First Step

  1. Select row and right click it > Copy AS > Select "Insert Statement" or "Update Statement"

Second Step

  1. Paste in New Query in MS SQL Server Management Studio or other IDE

Third Step

user_39881220
Related