SQL - Insert random values into a table

Question

Hello I am trying to insert random values in the columns but without success. I tried that but I got nothing. Someone who can help please.

Declare @NovosID int
SELECT @NovosID=NovosID FROM Loja.dbo.Itens_Ale WHERE UsuarioID = @UsuarioID

INSERT INTO Loja.dbo.S_Itens (UsuarioID, Vaga, ID, Quantidade, Código, Nome, Ordenar, Verificar, Data)
        VALUES (@UsuarioID, @Vagas, @NovosID, '1', 'Site', @ItemNome, @OrdenarNu, '1', @GetData);

I tried this gambiarra but without success. Inside the table_Ale has only 4 items with the ID 101/106/112/194 So I want him to make a random choice of these numbers to send to the other table.

Answer 1

Just use the function RAND to select a random integer value from a table. In the following example I created the table valores with an identification column (id) and with a column valor with the possible data:

CREATE TABLE valores(
  id    INTEGER NOT NULL,
  valor INTEGER NOT NULL
);

INSERT INTO valores(id, valor)
             VALUES(1, 101),
                   (2, 106),
                   (3, 112),
                   (4, 194);

After that, to fetch some value, just use the function RAND in the WHERE:

SELECT v.valor
  FROM valores v
 WHERE v.id = FLOOR(RAND() * 3 + 1)

---

RAND

Returns a value float 0 to 1 pseudorandom, exclusive.