How to do INSERT on three tables with Stored Procedure?

1

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?

    
asked by anonymous 17.02.2016 / 02:36

1 answer

3

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 .

    
17.02.2016 / 06:17