SQLTeam.com | Weblogs | Forums

Move data and delete

hi everyone,
i dont know much of sql but i need to move data from a table to another

i have done this:
> insert into etiquetasklc.dbo.etiquetas (id,Nome_posto, data, lote, serialnumber, partnumber,versao,indexnumber,semana,qrgerado,operador,snpn)
> select top 10 id, Nome_posto, data, lote, serialnumber, partnumber,versao,indexnumber,semana,qrgerado,operador,snpn from etiquetasklc.dbo.etiquetas2018
> where partnumber = '8613012704'
> order by id desc
>
> insert into etiquetasklc.dbo.etiquetas (id,Nome_posto, data, lote, serialnumber, partnumber,versao,indexnumber,semana,qrgerado,operador,snpn)
> select top 10 id, Nome_posto, data, lote, serialnumber, partnumber,versao,indexnumber,semana,qrgerado,operador,snpn from etiquetasklc.dbo.etiquetas2018
> where partnumber = '8613013770'
> order by id desc
>
> insert into etiquetasklc.dbo.etiquetas (id,Nome_posto, data, lote, serialnumber, partnumber,versao,indexnumber,semana,qrgerado,operador,snpn)
> select top 10 id, Nome_posto, data, lote, serialnumber, partnumber,versao,indexnumber,semana,qrgerado,operador,snpn from etiquetasklc.dbo.etiquetas2018
> where partnumber = '8613016017'
> order by id desc

but what i really need is maintaint the top 10 from each partnumber and move the rest to another table.
any help?

Hi please try

Select
Top 10
Rank() over( partition by partnumber order by some column )

insert into etiquetasklc.dbo.etiquetas (id,Nome_posto, data, lote, serialnumber,
    partnumber,versao,indexnumber,semana,qrgerado,operador,snpn)
select 
    id, Nome_posto, data, lote, serialnumber, 
    partnumber, versao, indexnumber, semana, qrgerado, operador, snpn
from (
    select id, Nome_posto, data, lote, serialnumber, 
        partnumber, versao, indexnumber, semana, qrgerado, operador, snpn,
        row_number() over(partition by partnumber order by id desc) as row_num
    from etiquetasklc.dbo.etiquetas2018
) as derived
where row_num <= 10