July 31, 2019 at 11:13 am
Need to move old data to Historic table, but the exixting query is this and i want to not to have to create on por each partnumber
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 = '8613015207'
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 = '8613012838'
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 = '8613011104'
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 = '8613015208'
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 = '8613011264'
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 = '8613013766'
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 = '8613010031'
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 = '8613012869'
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 = '8613012760'
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 = '8613015240'
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 = '8613010108'
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 = '8613013337'
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 = '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
Thanks
July 31, 2019 at 12:03 pm
try this one:
with data as
(
select
row_number() over (partition by partnumber order by id desc) [n]
,id, Nome_posto, data, lote, serialnumber, partnumber,versao,indexnumber,semana,qrgerado,operador,snpn
from etiquetasklc.dbo.etiquetas2018
)
/*
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 data
where n<11
I have no data to test, so first check that output is ok for you and only then uncomment the insert part
July 31, 2019 at 12:03 pm
Remove the where clause. then it does all products
July 31, 2019 at 4:17 pm
it work's
what if i wanna keep the top 10 of the products and send the rest to historic table, sorry for the abuse
August 1, 2019 at 9:15 am
the simple awnser is always the best.
Sorry for my ignorance
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply