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.
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
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;
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.