Inserindo registros via procedure (incrementando)

Question

Pessoal,

Tenho a seguinte query:

SELECT  id, item, ranking FROM gerp ORDER BY id

id    item        ranking
527    15393728    NULL
528    15393709    NULL
528    15393715    NULL
528    15393728    NULL
529    15393709    NULL
529    15393715    NULL
529    15393728    NULL
530    15393709    NULL
530    15393715    NULL

Preciso inserir registros nesta tabela no campo (ranking) incrementando.

Ex: Tenho o id 527                        então seta ranking 1
    Tenho o id 528                           então seta ranking 1
    Tenho o id 528 (novamente)     então seta ranking 2
    Tenho o id 528 (novamente)     então seta ranking 3
    Tenho o id 529 (novamente)     então seta ranking 1
    Tenho o id 529 (novamente)     então seta ranking 2
    Tenho o id 529 (novamente)     então seta ranking 3
    Tenho o id 530 (novamente)     então seta ranking 1
    Tenho o id 530 (novamente)     então seta ranking 2

Alguém tem um idéia para me ajudar, tentei fazer uma procedure, mais não deu certo...

Obrigado!

Answer 1

Carlos,

Segue um exemplo simples usando o ROW_NUMBER

declare @gerp table (id int, item int)

insert into @gerp values (527,15393728)
insert into @gerp values (528,15393709)
insert into @gerp values (528,15393715)
insert into @gerp values (528,15393728)
insert into @gerp values (529,15393709)
insert into @gerp values (529,15393715)
insert into @gerp values (529,15393728)
insert into @gerp values (530,15393709)
insert into @gerp values (530,15393715)

SELECT id, item, row_number() over(partition by id order by id,item) as 'ranking' FROM @gerp ORDER BY id

Att
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS.
Se útil, classifique!!!
Me siga no twitter: @marcelodba

Answer 2

Um... na verdade eu acho q ele esta procurando por isso aqui:

declare @gerp table (id int, item int, ranking int)

insert into @gerp select 529, 15393709, COUNT(ID) + 1 from @gerp WHERE ID = 529
insert into @gerp select 528, 15393709, COUNT(ID) + 1 from @gerp WHERE ID = 528
insert into @gerp select 528, 15393715, COUNT(ID) + 1 from @gerp WHERE ID = 528
insert into @gerp select 528, 15393728, COUNT(ID) + 1 from @gerp WHERE ID = 528
insert into @gerp select 527, 15393728, COUNT(ID) + 1 from @gerp WHERE ID = 527
insert into @gerp select 529, 15393728, COUNT(ID) + 1 from @gerp WHERE ID = 529
insert into @gerp select 528, 15393730, COUNT(ID) + 1 from @gerp WHERE ID = 528

select * from @gerp order by id, ranking

Answer 3

Rui,

Desta forma que vc passou está correta, eu executo e ele incrementa na ordem, porém depois faço um select na tabela e ele não inseriu o registro.

Será q faltou algo?

Obrigado.

Answer 4

Rui,

não é o mesmo efeito do row_number? ou tem algo que estou deixando passar?

Att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS.
Se útil, classifique!!!
Me siga no twitter: @marcelodba

Answer 5

Criei a procedure só para testar, eu executo ela, só que ele não insere o registro com o contador incrementando...

CREATE PROCEDURE dbo.sp_gerar
AS
DECLARE @gerar (id int, item bigint, ranking int)
BEGIN TRANSACTION
insert into @gerar select 527, 15393709, COUNT(ID) + 1 from @gerar WHERE ID = 527
insert into @gerar select 527, 15393715, COUNT(ID) + 1 from @gerar WHERE ID = 527
insert into @gerar select 527, 15393728, COUNT(ID) + 1 from @gerar WHERE ID = 527
ROLLBACK TRANSACTION
--select * from @gerar_pdf_ordem order by id, ranking

Answer 6

Carlos,

me tira uma dúvida, vc ja tem os dados em uma tabela e deseja incluir estes dados em outra tabela adicionando a coluna ranking? ou vc deseja incluir um regristro em uma tabela ja existente atualizando o ranking?

Att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS.
Se útil, classifique!!!
Me siga no twitter: @marcelodba

Answer 7

se for uma tabela ja existente e vc quer inserir em outra tabela incluindo o campo ranking teste o script abaixo...

create PROCEDURE dbo.sp_gerar
as
declare @gerp table (id int, item int)

insert into @gerp values (527,15393728)
insert into @gerp values (528,15393709)
insert into @gerp values (528,15393715)
insert into @gerp values (528,15393728)
insert into @gerp values (529,15393709)
insert into @gerp values (529,15393715)
insert into @gerp values (529,15393728)
insert into @gerp values (530,15393709)
insert into @gerp values (530,15393715)

declare @gerar table (id int, item bigint, ranking int)
insert into @gerar
SELECT id, item, row_number() over(partition by id order by id,item) as 'ranking' FROM @gerp ORDER BY id
select * from @gerar

go
exec sp_gerar

Att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS.
Se útil, classifique!!!
Me siga no twitter: @marcelodba

Answer 8

Marcelo,

Tenho a tabela gerar com os seguintes campos id, item e ranking, os campos id e item estão preenchidos o que necessito é de inserir os registros incrementando no campo ranking (validando com o id)...

 

 

 

Answer 9

Marcelo só para esclarecer melhor, quando digo o campo Ranking não é que estou utilizando a função RANK...tenho um campo mesmo com esse nome ranking (que será inserido os registros para rankear os registros).

 

Answer 10

segue novo tento... no caso uso a função row_number para calcular este ranking....

create PROCEDURE dbo.sp_gerar
as
declare @gerp table (id int, item int, ranking int)

insert into @gerp values (527,15393728,null)
insert into @gerp values (528,15393709,null)
insert into @gerp values (528,15393715,null)
insert into @gerp values (528,15393728,null)
insert into @gerp values (529,15393709,null)
insert into @gerp values (529,15393715,null)
insert into @gerp values (529,15393728,null)
insert into @gerp values (530,15393709,null)
insert into @gerp values (530,15393715,null)

;with cte_dados
as (
SELECT id, item,ranking, row_number() over(partition by id order by id,item) as 'agrupador' FROM @gerp 
)

update cte_dados
set ranking= agrupador

select * from @gerp
go
exec sp_gerar

att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS.
Se útil, classifique!!!
Me siga no twitter: @marcelodba

Answer 11

Testei das duas formas e ele me retorna o resultado correto, porém ainda não consegui inseri-los na tabela...

neste insert eu não teria que guardar o null em variável para ser o agrupador?

insert into @gerar_pdf_ordem values (527,15393728,null ) 

Porque nesta query retorna o resultado certo tb...

 

SELECT id, item,ranking, row_number() over(partition by id order by id,item) as 'agrupador' FROM @gerp 

Answer 12

Carlos,

não precisa guardar o null pq hoje vc não tem informações na sua tabela, o que entendi é que vc deseja alterar este null pelo ranking (que a CTE faz o trabalho) para você aplicar em seu ambiente basta apenas criar a proc com o CTE e o update trocando a variavel table pela sua tabela... no exemplo criei a variavel table para simular seu ambiente...

Att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS.
Se útil, classifique!!!
Me siga no twitter: @marcelodba

Answer 13

Rui,

não é o mesmo efeito do row_number? ou tem algo que estou deixando passar?

Att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS.
Se útil, classifique!!!
Me siga no twitter: @marcelodba

Primeiramente.. row_number e o over não funcionam no SQL SERVER 2000, então é bom não ficar muito viciado em soluções desse tipo... alem de não serem SQL ANSI...

E eu segui a especificação do problema... pode ate ser q o row_number sirva para a ordenação dos itens... mas não foi o que ele pediu...

ideia é ter uma sequencia para cada codigo independente.. gravada na tabela...

207    1
208    1
209    1
208    2
209    2

208    3

Answer 14

Carlos... o @ na frente do nome da tabela é pq ela é uma variavel que a gente criou para testes... vc tem que colocar o nome da sua tabela sem o "@"

insert into gerp select 529, 15393709, COUNT(ID) + 1 from gerp WHERE ID = 529
insert into gerp select 528, 15393709, COUNT(ID) + 1 from gerp WHERE ID = 528
insert into gerp select 528, 15393715, COUNT(ID) + 1 from gerp WHERE ID = 528
insert into gerp select 528, 15393728, COUNT(ID) + 1 from gerp WHERE ID = 528
insert into gerp select 527, 15393728, COUNT(ID) + 1 from gerp WHERE ID = 527
insert into gerp select 529, 15393728, COUNT(ID) + 1 from gerp WHERE ID = 529
insert into gerp select 528, 15393730, COUNT(ID) + 1 from gerp WHERE ID = 528

select * from gerp order by id, ranking

monta o procedure assim

create procedure IncluiGerp(@ID int, @Item int)
as
begin
   insert into gerp select @ID, @ITEM, COUNT(ID) + 1 from gerp WHERE ID = @ID
end

Exec IncluiGerp(529, 15393709)

Answer 15

segue novo tento... no caso uso a função row_number para calcular este ranking....

create PROCEDURE dbo.sp_gerar

as

declare @gerp table (id int, item int, ranking int)



insert into @gerp values (527,15393728,null)

insert into @gerp values (528,15393709,null)

insert into @gerp values (528,15393715,null)

insert into @gerp values (528,15393728,null)

insert into @gerp values (529,15393709,null)

insert into @gerp values (529,15393715,null)

insert into @gerp values (529,15393728,null)

insert into @gerp values (530,15393709,null)

insert into @gerp values (530,15393715,null)



;with cte_dados

as (

SELECT id, item,ranking, row_number() over(partition by id order by id,item) as 'agrupador' FROM @gerp 

)



update cte_dados

set ranking= agrupador



select * from @gerp

go

exec sp_gerar

att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS.
Se útil, classifique!!!
Me siga no twitter: @marcelodba

Ah.. Marcelo... vc esta dando o Update em todos os registros para cada inserção ai....
Se for uma tabela com algumas centenas de milhares de registros... cada insert vai ficar um pouco custoso...

Answer 16

Entendi a idéia de vcs agora, fiz o insert aqui ajustando no meu ambiente e ele inseriu os registros normalmente, agora vou verificar se inseriu de forma correta.

Answer 17

segue novo tento... no caso uso a função row_number para calcular este ranking....

create PROCEDURE dbo.sp_gerar



as



declare @gerp table (id int, item int, ranking int)







insert into @gerp values (527,15393728,null)



insert into @gerp values (528,15393709,null)



insert into @gerp values (528,15393715,null)



insert into @gerp values (528,15393728,null)



insert into @gerp values (529,15393709,null)



insert into @gerp values (529,15393715,null)



insert into @gerp values (529,15393728,null)



insert into @gerp values (530,15393709,null)



insert into @gerp values (530,15393715,null)







;with cte_dados



as (



SELECT id, item,ranking, row_number() over(partition by id order by id,item) as 'agrupador' FROM @gerp 



)







update cte_dados



set ranking= agrupador







select * from @gerp



go



exec sp_gerar

att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS.
Se útil, classifique!!!
Me siga no twitter: @marcelodba

Ah.. Marcelo... vc esta dando o Update em todos os registros para cada inserção ai....
Se for uma tabela com algumas centenas de milhares de registros... cada insert vai ficar um pouco custoso...

Rui fiquei confuso denovo :)
tinha entendido que ele ja tinha os dados na tabela e queria apenas atualizar o campo ranking que estava nulo... mas acho que seu entendimento foi o correto, a intenção é popular outra tabela com mais este campo...

---

MCP, MCDBA, MCSA, MCTS.
Se útil, classifique!!!
Me siga no twitter: @marcelodba

Answer 18

Agradeço a ajuda de vocês, ele inseriu os registros...porém não sei porque alguns ele inseriu de forma incorreta na ordenação...

Mais esta solução funcionou, vou tentar apenas adapta-la agora.

Obrigado!

Answer 19

Rui fiquei confuso denovo :)
tinha entendido que ele ja tinha os dados na tabela e queria apenas atualizar o campo ranking que estava nulo... mas acho que seu entendimento foi o correto, a intenção é popular outra tabela com mais este campo...

---

MCP, MCDBA, MCSA, MCTS.
Se útil, classifique!!!
Me siga no twitter: @marcelodba

Ok.. agora vc me confundiu... :S achei que ele ia cadastrar esses item numa tabela em branco....