Export query result as insert scripts

1

I would like to export the result of a query in SQL Server as an insert script to insert the data into another database.

I know there is an option to export the whole table from the menu "Tasks -> Generate Scripts" but I do not want to export the whole table, I need only a few records to test in another database.

ps: Banks are not on the same server.

    
asked by anonymous 04.04.2017 / 13:47

3 answers

1

Normally when I need something like this I make a SELECT that manages the INSERT script at hand, here's an example of how I usually do it.

Considering% co

04.04.2017 / 15:02
0

You can use procedure 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 look something like this:

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

If you did not want to use where , you can use the @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;
    
04.04.2017 / 15:05
-1

You can export / import directly between databases without the need for intermediate files, and delimiting the WHERE clause the restrictions to get " only a few records ". Just have permission to access both databases.

If both databases are in the same instance:

-- código #1 v3
USE outrobanco;

INSERT into tabela 
  SELECT ...
    from banco.dbo.tabela
    where ...

But if the databases are in different instances, you first need to link the source database to the "other database". Information on Create Linked Servers . Afterwards,

-- código #2
USE outrobanco;

INSERT into tabela 
  SELECT ...
    from servidor.banco.dbo.tabela
    where ...

GENERATE_INSERTS
But, if you even need an intermediate file, evaluate whether the generate_inserts script meets what you need. Documentation included in the script.

    
04.04.2017 / 13:59