Generate insert script for selected records?

Question

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.

The famous Vyas script http://vyaskn.tripod.com/code/generate_inserts.txt — Madhivanan, Jul 19 '12 at 12:20
Conditional insert statements can also be create using Visual Studio 2013: http://stackoverflow.com/a/36426678/4424024 — Martin, Apr 05 '16 at 12:42

score 49 · Accepted Answer · answered Jul 19 '12 at 12:20

49

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

answered Jul 19 '12 at 12:20

Tamir

3,525
3
28
41

my destnation table has PkID as Identity but i made set identity on and it worked – sudhAnsu63 Jul 19 '12 at 14:49
@Tamir How perform this operation using SMO library? – Jalal Jun 07 '15 at 11:45
@Jalal, take a look here: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.aspx you will find a property named "scripter". I think that will do the work – Tamir Jun 07 '15 at 13:20
If migrating to new database,In Visual Studio 2013 there is Data Comparison tool which will help you to **Auto Generate Script** from source database to Run in Target Database check [this](https://channel9.msdn.com/Events/Visual-Studio/Launch-2013/VS108) video or [this](http://stackoverflow.com/a/685073/2218697) post, hope helps someone. – Shaiju T Dec 09 '15 at 09:12
28

But this answer's solution does not generate an "insert" script as the original question requested. It only allows you to use a query to get a dataset and then export the dataset to a destination, which is not an insert script. I am using SSMS 2012. – jyao Apr 23 '16 at 03:13
4

@jyao Correct, this does not give an insert query. What I did was I exported the data to a new temporary table that I made and then generated a Data script from there using the `Generate Scripts` option on the Database. That exported only the needed records. It's quite a hassle but I haven't found another option to directly export records to a new query. – Vinz May 17 '17 at 16:38
3

Is there a way to have INSERT queries as results? – Cirelli94 Mar 29 '18 at 12:31

score 30 · Answer 2 · edited Aug 19 '20 at 23:12

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

Open Visual Studio
Open "View" → "SQL Server Object Explorer"
Add a connection to your Server
Expand the relevant database
Expand the "Tables" folder
Right click on relevant table
Select "View Data" from context menu
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.
After you have applied your filter and see only the data you want, click on "Script" or "Script to file" in the tool bar
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)

Nice suggestion. One drawback noticed: binary data is exported as ``. — Alexei - check Codidact, Oct 05 '20 at 08:58
You can write a query from Visual Studio and perform a right click -> export data from the limited result set, rather than doing so from the UI and its limited features. From step 4 above, right click on your database and select "New Query". Write your query with conditions in the where clause, and execute it. In the results, right click and you'll be presented with a "Script as Inserts" option. You'll need to do a minor amount of massaging since it generates the insert into a global temp table. — DarkSkyForever, Oct 16 '20 at 16:00

score 23 · Answer 3 · edited Apr 05 '19 at 07:51

23

CREATE PROCEDURE sp_generate_insertscripts
(
    @TABLENAME VARCHAR(MAX),
    @FILTER_CONDITION VARCHAR(MAX)=''   -- where TableId = 5 or some value
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @TABLE_NAME VARCHAR(MAX),
        @CSV_COLUMN VARCHAR(MAX),
        @QUOTED_DATA VARCHAR(MAX),
        @TEXT VARCHAR(MAX),
        @FILTER VARCHAR(MAX) 

SET @TABLE_NAME=@TABLENAME

SELECT @FILTER=@FILTER_CONDITION

SELECT @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 TEXT

EXECUTE (@TEXT)

SET NOCOUNT OFF

END

edited Apr 05 '19 at 07:51

answered Apr 06 '16 at 09:30

Param Yadav

643
6
5

3

Please provide your answer, with proper explanation and proper alignment. Just posting your code is not enough. – Karthikeyan Apr 06 '16 at 09:43
@Karthikeyan I will provide the step by step explantion of my sp after few days when i got time. – Param Yadav Apr 06 '16 at 15:20
@Karthikeyan see my edited post through this link for explanation of above query http://stackoverflow.com/questions/4526461/converting-select-results-into-insert-script – Param Yadav Apr 07 '16 at 08:51
This is awesome. I noticed that you need to add a space in the TABLENAME param so that it does not error out by concatenating with the FILTER_CONDITION – flerngobot Sep 15 '17 at 22:28
I have 'Key' column name in the table and it failed to create an insert script. Error: Incorrect syntax near the keyword 'Key'. – H_H Jul 17 '19 at 06:34
you need to wrap key column from select list into square bracket because key is SQL keyword that's it showing error if not solve after trying this please provide screenshot – Param Yadav Jul 18 '19 at 07:16
This is the solution I was looking for. It is flexible for any table. I made a few small changes. At the end of the longest line, change `+ @FILTER` to `+ IIF(@FILTER='','',' WHERE ' + @FILTER)` so that you can pass a filter without including the word `WHERE`. Another change was to add `AND system_type_id <> 189 AND is_computed=0` within the WHERE of the two SELECT queries to ignore *timestamp* and *computed* columns. It would be easy to add additional exclusions for other specialty column types. – Ben Sep 30 '20 at 22:42

score 19 · Answer 4 · answered Jul 19 '12 at 12:22

19

SELECT 'INSERT SomeOtherDB.dbo.table(column1,column2,etc.)
  SELECT ' + CONVERT(VARCHAR(12), Pk_Id) + ','
       + '''' + REPLACE(ProductName, '''', '''''') + ''','
       + CONVERT(VARCHAR(12), Fk_CompanyId) + ','
       + CONVERT(VARCHAR(12), Price) + ';'
FROM dbo.unspecified_table_name
WHERE Fk_CompanyId = 1;

answered Jul 19 '12 at 12:22

Aaron Bertrand

242,666
35
420
451

How do you execute(run) those generated insert statement? – J.W. Mar 09 '15 at 10:54
@J.W. Copy & paste? You could also do something like `SELECT @sql = @sql + N'INSERT ...;' FROM ...`. – Aaron Bertrand Mar 09 '15 at 13:54
2

Just a note to anyone using this: `select null +'x'` is `NULL`, so don't forget to initialise `@sql` to a non-null value, otherwise your whole statement will concatenate to `NULL`. Got me puzzled for 10 minutes. – Darius Dec 07 '15 at 11:33
Tanx, simple and useful :) – reza akhlaghi Aug 13 '17 at 05:11

score 5 · Answer 5 · answered Jul 19 '12 at 12:20

5

You could create a view with your criteria and then export the view?

answered Jul 19 '12 at 12:20

bleeeah

3,306
17
25

1

SSMS generate scripts will not export the data even if Data only is enforced. – Alexei - check Codidact Oct 05 '20 at 09:24

score 5 · Answer 6 · answered May 04 '16 at 15:48

5

I ended up doing this in 2 steps. Selected the records I want into a new table in the database then generated a SQL data only script in SSMS. I did find and replace on the generated script and removed the table.

answered May 04 '16 at 15:48

Steve Greene

11,089
1
27
45

good solution, works if you have the write access on your source database. Wasn't the case for me. – Paramvir Singh Karwal Jun 30 '19 at 09:32
This seems to be only solution if you need to also export VARBINARY data. SSMS Generate script knows how to correctly export these, but does not work with views (exports schema only despite the option to also export the data). Requires more steps, but does the job. Thanks. – Alexei - check Codidact Oct 05 '20 at 09:26

score 4 · Answer 7 · answered Nov 08 '16 at 09:46

4

In SSMS execute your sql query. From the result window select all cells and copy the values. Goto below website and there you can paste the copied data and generate sql scripts. You can also save results of query from SSMS as CSV file and import the csv file in this website.

http://www.convertcsv.com/csv-to-sql.htm

answered Nov 08 '16 at 09:46

JC Raja

4,637
2
15
9

Nice site, works pretty well in most cases although more complex strings/large records didn't work for me when "copy with headers" from SSMS and pasting on the site. When I "save results as" to a .csv file and used upload it worked. – Jan Sep 25 '18 at 14:57
Will not handle 32 million rows. – Micah Epps Jun 24 '19 at 15:17

score 2 · Answer 8 · answered Apr 07 '17 at 13:25

I created the following procedure:

if object_id('tool.create_insert', 'P') is null
begin
  exec('create procedure tool.create_insert as');
end;
go

alter procedure tool.create_insert(@schema    varchar(200) = 'dbo',
                                   @table     varchar(200),
                                   @where     varchar(max) = null,
                                   @top       int = null,
                                   @insert    varchar(max) output)
as
begin
  declare @insert_fields varchar(max),
          @select        varchar(max),
          @error         varchar(500),
          @query         varchar(max);

  declare @values table(description varchar(max));

  set nocount on;

  -- Get columns
  select @insert_fields = isnull(@insert_fields + ', ', '') + c.name,
         @select = case type_name(c.system_type_id)
                      when 'varchar' then isnull(@select + ' + '', '' + ', '') + ' isnull('''''''' + cast(' + c.name + ' as varchar) + '''''''', ''null'')'
                      when 'datetime' then isnull(@select + ' + '', '' + ', '') + ' isnull('''''''' + convert(varchar, ' + c.name + ', 121) + '''''''', ''null'')'
                      else isnull(@select + ' + '', '' + ', '') + 'isnull(cast(' + c.name + ' as varchar), ''null'')'
                    end
    from sys.columns c with(nolock)
         inner join sys.tables t with(nolock) on t.object_id = c.object_id
         inner join sys.schemas s with(nolock) on s.schema_id = t.schema_id
   where s.name = @schema
     and t.name = @table;

  -- If there's no columns...
  if @insert_fields is null or @select is null
  begin
    set @error = 'There''s no ' + @schema + '.' + @table + ' inside the target database.';
    raiserror(@error, 16, 1);
    return;
  end;

  set @insert_fields = 'insert into ' + @schema + '.' + @table + '(' + @insert_fields + ')';

  if isnull(@where, '') <> '' and charindex('where', ltrim(rtrim(@where))) < 1
  begin
    set @where = 'where ' + @where;
  end
  else
  begin
    set @where = '';
  end;

  set @query = 'select ' + isnull('top(' + cast(@top as varchar) + ')', '') + @select + ' from ' + @schema + '.' + @table + ' with (nolock) ' + @where;

  insert into @values(description)
  exec(@query);

  set @insert = isnull(@insert + char(10), '') + '--' + upper(@schema + '.' + @table);

  select @insert = @insert + char(10) + @insert_fields + char(10) + 'values(' + v.description + ');' + char(10) + 'go' + char(10)
    from @values v
   where isnull(v.description, '') <> '';
end;
go

Then you can use it that way:

declare @insert varchar(max),
        @part   varchar(max),
        @start  int,
        @end    int;

set @start = 1;

exec tool.create_insert @schema = 'dbo',
                        @table = 'myTable',
                        @where  = 'Fk_CompanyId = 1',
                        @insert = @insert output;

-- Print one line to avoid the maximum 8000 characters problem
while len(@insert) > 0
begin
  set @end = charindex(char(10), @insert);

  if @end = 0
  begin
    set @end = len(@insert) + 1;
  end;

  print substring(@insert, @start, @end - 1);
  set @insert = substring(@insert, @end + 1, len(@insert) - @end + 1);
end;

The output would be something like that:

--DBO.MYTABLE
insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(1, 'AMX', 1, 10.00);
go

insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(2, 'ABC', 1, 11.00);
go

insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(3, 'APEX', 1, 12.00);
go

insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(4, 'AMX', 1, 10.00);
go

insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(5, 'ABC', 1, 11.00);
go

insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(6, 'APEX', 1, 12.00);
go

insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(7, 'AMX', 2, 10.00);
go

insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(8, 'ABC', 2, 11.00);
go

insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(9, 'APEX', 2, 12.00);
go

If you just want to get a range of rows, use the @top parameter as bellow:

declare @insert varchar(max),
        @part   varchar(max),
        @start  int,
        @end    int;

set @start = 1;

exec tool.create_insert @schema = 'dbo',
                        @table = 'myTable',
                        @top    = 100,
                        @insert = @insert output;

-- Print one line to avoid the maximum 8000 characters problem
while len(@insert) > 0
begin
  set @end = charindex(char(10), @insert);

  if @end = 0
  begin
    set @end = len(@insert) + 1;
  end;

  print substring(@insert, @start, @end - 1);
  set @insert = substring(@insert, @end + 1, len(@insert) - @end + 1);
end;

Looks promising, but I get an issue where both varchar values and datetime values are printed without surrounding quotes. Example: `values(18, Food processor, 106, 2017-12-07 15:17:35, 0, 1)` — Peter B, Dec 14 '17 at 16:52
@PeterB can you check if you're using `varchar` or `nvarchar`? — Sorack, Dec 14 '17 at 17:35

score 0 · Answer 9 · answered Mar 10 '21 at 12:34

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

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

This will give you insert statements

score -1 · Answer 10 · answered Nov 30 '15 at 05:09

-1

If you are using Oracle SQL Developer then it would be

select /*insert*/ * from TABLE_NAME where COLUMN_NAME = 'VALUE';

Run this as a script

answered Nov 30 '15 at 05:09

Varun Rao

751
1
7
26

Generate insert script for selected records?

10 Answers10

Linked