Generate Inserts from a populated table

Question

I have a populated table and I need to generate the scripts of insert of the data stored in it, I cannot export, it has to be the script of insert into ... Do for select concatenating is very laborious, since I have to do this in more than one table and they all have many fields. I wonder if there is a procedure/program that manages the inserts of the data in this table.

We use SqlServer - 2016

Answer 1

You can use the procedure down below:

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

alter procedure ferramenta.gerar_insercao(@schema    varchar(200) = 'dbo',
                                          @tabela    varchar(200),
                                          @where     varchar(max) = null,
                                          @top       int = null,
                                          @insert    varchar(max) output)
as
begin
  declare @insert_campos varchar(max),
          @select        varchar(max),
          @erro          varchar(500),
          @query         varchar(max);

  declare @valores table(descricao varchar(max));

  set nocount on;

  -- Pega a relação de colunas
  select @insert_campos = isnull(@insert_campos + ', ', '') + 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 = @tabela;

  -- Caso não tenha encontrado as colunas...
  if @insert_campos is null or @select is null
  begin
    set @erro = 'Tabela ' + @schema + '.' + @tabela + ' não existe na base de dados.';
    raiserror(@erro, 16, 1);
    return;
  end;

  set @insert_campos = 'insert into ' + @schema + '.' + @tabela + '(' + @insert_campos + ')';

  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 + '.' + @tabela + ' with (nolock) ' + @where;

  insert into @valores(descricao)
  exec(@query);

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

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

As follows:

declare @insert varchar(max),
        @parte  varchar(max),
        @inicio int,
        @final  int;

set @inicio = 1;

exec ferramenta.gerar_insercao @schema = 'dbo',
                               @tabela = 'cliente',
                               @where  = 'codigo = 1',
                               @insert = @insert output;

-- Dá um comando "print" a cada quebra de linha para não estourar o tamanho máximo de 8000
while len(@insert) > 0
begin
  set @final = charindex(char(10), @insert);

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

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

The result would be something like this:

--DBO.CLIENTE
insert into dbo.cliente(codigo, nome, tipo)
values(1, 'NOME DO CLIENTE', 'F');
go

If you didn’t want to use the where, you can use the parameter @top:

declare @insert varchar(max),
        @parte  varchar(max),
        @inicio int,
        @final  int;

set @inicio = 1;

exec ferramenta.gerar_insercao @schema = 'dbo',
                               @tabela = 'cliente',
                               @top    = 100,
                               @insert = @insert output;

-- Dá um comando "print" a cada quebra de linha para não estourar o tamanho máximo de 8000
while len(@insert) > 0
begin
  set @final = charindex(char(10), @insert);

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

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

Answer 2

It is also possible by Managment Studio (SSMS)...

1. Right Click on your Database and Go to Tasks -> Generate Scripts

2. Specify the tables you want to export. After this, click Next.

3. Under Set Script Options, click the Advanced button.

4. Change the Types of data to script parameter to Data Only. This will define whether to export the Inserts with the data from your table.

5. Finish and see the result...