I have a populated table and need to generate the scripts of insert of the data stored in it, I can not export, it must be script of insert into ...
Doing with select concatenating is very laborious, since I have to do this in more than one table and they all have many fields. I would like to know if there is a procedure / program that manages the inserts of the data in this table.
We use SqlServer - 2016
It is also possible by Managment Studio (SSMS) ...
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;