[sql] Generate insert script for selected records?

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.

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

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)

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;

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

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.

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

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

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;