generate a numerical sequence in an insert into from

Question

generate a numerical sequence in an insert into from

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

hello I have a table that has no identity on its pk (since it is generated through a program that fills the table). I must enter records from another table, but I need a function to generate this id. I have the following insert to add the data.

insert into movctble (movctbleid, MovctbleGlosa,MovctbleFecha,MovctbleEstado,MovctbleUserId,MovctbleUpdFhora,MovctbleClienteId) 
select liquidadescripcion, liquidafecha, liquidaestado, 0, liquidafecha, liquidacuenta from XAsientosFaltantes

but I'm missing the first field. How can I generate this number automatically? Greetings

sql-server

asked by Luis Gabriel Fabres 30.08.2018 в 20:13

source

2 answers

1

use this

insert into movctble (movctbleid, MovctbleGlosa,MovctbleFecha,MovctbleEstado,MovctbleUserId,MovctbleUpdFhora,MovctbleClienteId) 
select ROW_NUMBER() OVER(ORDER BY liquidadescripcion ASC) AS Row, liquidadescripcion, liquidafecha, liquidaestado, 0, liquidafecha, liquidacuenta from XAsientosFaltantes

answered by 30.08.2018 в 20:38

How to store variable in JavaScript until reloading page? Do Hover in an image and it stops immediately with Slick Slider

score 1 · Accepted Answer

You can add the function row_number() OVER (ORDER BY liquidafecha) to the select, that gives you a sequential numbering, but if you want to follow the sequence of the table, you must first obtain the last value of that sequence and add it to the result of the function.

declare @variable numeric(18,0)
select @variable=max(SECUENCIAL) FROM XAsientosFaltantes

. .

SELECT @variable+row_number() OVER (ORDER BY liquidafecha) FROM XAsientosFaltantes