How to do INSERT in three tables with Stored Procedure?

Question

I need to perform a 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 in Sql Server for this?

Answer 1

There are several ways ! In one PROCEDURE you can create your logic .

Let me give you an example, but I don’t understand exactly what you want to do and I don’t have your table structure

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 inserting parents, 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 trial just run

 exec InserirTudoJunto 'Brasil','Minas Gerais','Belo Horizonte'

Obs: when one or more Insert/update/delete depend on others, not to take the risk that one works and the other does not , it is important that they are made within a transaction.