I need to perform an insert in three related tables in the same query. I have the following scenario: Cidade, Estado e Pais
How can I build a stored procedure on Sql Server for this?
I need to perform an insert in three related tables in the same query. I have the following scenario: Cidade, Estado e Pais
How can I build a stored procedure on Sql Server for this?
There are several ways! In a PROCEDURE you can create your logic.
I'll give you an example, however I did not understand exactly what you want to do and I do not have the structure of your table
With the following scheme:
create table pais(cod_pais int identity primary key,nome nvarchar(50))
go
create table estado(cod_estado int identity primary key,cod_pais int foreign key references pais,nome nvarchar(50))
go
create table cidade(cod_cidade int identity primary key,cod_estado int foreign key references estado,nome nvarchar(50));
go
You can create a procedure that inserts country , state and city together:
create procedure InserirTudoJunto @nome_pais nvarchar(50) , @nome_estado nvarchar(50) , @nome_cidade nvarchar(50)
as
begin
begin transaction t
begin try
declare @ids table (cod_pais int,cod_estado int);
insert into pais (nome) output inserted.cod_pais into @ids(cod_pais) values ('Brasil');
insert into estado (nome,cod_pais) output inserted.cod_estado into @ids(cod_estado) values (@nome_estado,(select cod_pais from @ids));
insert into cidade (nome,cod_estado) values(@nome_cidade,(select cod_estado from @ids where cod_estado is not null));
commit transaction t
end try
begin catch
rollback transaction t
end catch
end
And now to run the procedure just run
exec InserirTudoJunto 'Brasil','Minas Gerais','Belo Horizonte'
Note: When one or more insert / delete / delete operations depend on others, the other is not, it is important that they be made within a transaction .