hi
i have 4000 records, and i need to pick randomize 500 records.
i need that 500 records will insert to GoodTable
and the rest 3500 records will insert to BadTable
how to do it using sql-server 2008 query ?
thank's in advance
+4
A:
This should work in Transact-SQL:
insert into GoodTable
select top 500 * from OtherTable order by newid()
EDIT: this might be better, it'll create the GoodTable for you automatically (instead of needing to create it manually beforehand with appropriate columns):
select top 500 *
into GoodTable
from OtherTable
order by newid()
tzaman
2010-05-02 14:13:24
Are you sure this will be random? It is dependent upon how the query engine fetches the TOP rows? E.g. might it fetch the same 500 rows each time, and just re-orders them because of the random sort order? To be totally sure the selection is random, would it not be wiser to use a subquery to assign a newid to all rows in the base table, and then select the top 500 from that?
mdma
2010-05-02 14:21:32
That's what `order by newid()` does already - it selects the top 500 *after* the ordering, which is done by a randomly generated `newid` for each row. I've used it myself on SQL Server 2008, works fine. Easy way to check: `select top 1 * ... order by newid()` should get you a different row every time.
tzaman
2010-05-02 14:25:34