Generate insert script for selected records? Generate insert script for selected records? database database

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


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 filterdataset" 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)


CREATE PROCEDURE sp_generate_insertscripts(    @TABLENAME VARCHAR(MAX),    @FILTER_CONDITION VARCHAR(MAX)=''   -- where TableId = 5 or some value)ASBEGINSET NOCOUNT ONDECLARE @TABLE_NAME VARCHAR(MAX),        @CSV_COLUMN VARCHAR(MAX),        @QUOTED_DATA VARCHAR(MAX),        @TEXT VARCHAR(MAX),        @FILTER VARCHAR(MAX) SET @TABLE_NAME=@TABLENAMESELECT @FILTER=@FILTER_CONDITIONSELECT @CSV_COLUMN=STUFF(    (     SELECT ',['+ NAME +']' FROM sys.all_columns      WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND      is_identity!=1 FOR XML PATH('')    ),1,1,'')SELECT @QUOTED_DATA=STUFF(    (     SELECT ' ISNULL(QUOTENAME('+NAME+','+QUOTENAME('''','''''')+'),'+'''NULL'''+')+'','''+'+' FROM sys.all_columns      WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND      is_identity!=1 FOR XML PATH('')    ),1,1,'')SELECT @TEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER--SELECT @CSV_COLUMN AS CSV_COLUMN,@QUOTED_DATA AS QUOTED_DATA,@TEXT TEXTEXECUTE (@TEXT)SET NOCOUNT OFFEND