Export query output as Insert scripts

Question

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

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

ps: Banks are not on the same server.

Answer 1

Usually when I need something like this I make one SELECT who manages the script for me INSERT at hand, follows an example of how I normally do.

Considering C1 INT and C2 and C3 STRING.

SELECT 'INSERT INTO TB1 (C1, C2, C3) VALUES (' + CAST(C1 AS VARCHAR) + ',''' + C2 + ''',''' + C3 + ''')'
FROM TB2

Note in Sqlserver two quotes, '', is transformed into quotation marks '

When you run this select it will generate several lines of INSERT, in which you will only need to run on your other DB

Answer 2

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 3

You can export/import directly between databases, without the need for intermediate files, and by delimiting in the WHERE clause the restrictions to obtain "only a few records".
Just have access to 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, it is first necessary to link the source database to the "other database". Information in Create linked servers. After,

-- código #2
USE outrobanco;

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

---

GENERATE_INSERTS
But if you really need an intermediate file, evaluate the script generate_inserts meets your needs. Documentation included in the script.