Pick random number within a range

Question

I have a table (TableA) with an integer column(ColumnA) and there is data already in the table. Need to write a select statement to insert into this table with the integer column having random values within 5000. This value should not already be in columnA of TableA

Insert into TableA (columnA,<collist....>)

SELECT <newColId> ,<collist....> from TableB <where clause>

I hope this [article](http://www.sqlperformance.com/2013/09/t-sql-queries/random-collisions) helps. — Bogdan Sahlean, Sep 29 '13 at 10:49

Roman Pekar · Accepted Answer · 2013-09-29T14:17:08.257

you can create a helper numbers table for this:

-- create helper numbers table, faster than online recursive CTE
-- can use master..spt_values, but actually numbers table will be useful
-- for other tasks too
create table numbers (n int primary key)

;with cte_numbers as (
    select 1 as n
    union all
    select n + 1 from cte_numbers where n < 5000
)
insert into numbers
select n
from cte_numbers
option (maxrecursion 0);

and then insert some numbers you don't have in TableA (using join on row_number() so you can insert multiple rows at once):

;with cte_n as (
    select n.n, row_number() over(order by newid()) as rn
    from numbers as n
    where not exists (select * from tableA as t where t.columnA = n.n)  
), cte_b as (
    select
        columnB, row_number() over(order by newid()) as rn
    from tableB
)
insert into TableA(columnA, columnB)
select n.n, b.ColumnB
from cte_b as b
    inner join cte_n as n on n.rn = b.rn

If you're sure that there could be only one row from TableB which will be inserted, you can use this query

insert into TableA(columnA, columnB)
select
    a.n, b.columnB
from tableB as b
    outer apply (
        select top 1 n.n
        from numbers as n
        where not exists (select * from tableA as t where t.columnA = n.n)
        order by newid() 
    )  as a

Note it's better to have index on ColumnA column to check existence faster.

sql fiddle demo

I suspect your nested subquery will affect performance negatively as datasize increases. — Menelaos, Sep 29 '13 at 14:09
I really think there're would be no difference in performance (or left join would be worse), do you have any tests? — Roman Pekar, Sep 29 '13 at 14:18
See: http://stackoverflow.com/a/2577224/1688441 . I may be wrong, but it's a relevant concern that needs to be addressed. — Menelaos, Sep 29 '13 at 14:22
@meewoK reed this article about anti-joins - http://www.sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join, and add some tests or it would be just your guesses — Roman Pekar, Sep 29 '13 at 14:25

Pick random number within a range

1 Answers1