Random selection but more than total count

Transact-SQL
1

hi,

i want to randomly select 40 rows from a table that has 30 rows only.

when i do the following code it only says its copied over 30 rows:

INSERT INTO TABLE_B
SELECT TOP 40 * FROM TABLE_A 
ORDER BY NEWID()

is there a way to copy 30 and then copy 10 more, so those 10 will be third duplicates?

thanks

2

You certainly have some interesting requirements from your few threads!

This should hopefully do the trick

--Test Table 30 rows
DECLARE @TableA TABLE
(
	VAL CHAR(1) NOT NULL
);

--Use Number table to generate test data
INSERT INTO @TableA (VAL)
SELECT	CHAR(N)
FROM	dbo.GetNums(65,95)


DECLARE @Top INT = 40,
		@RC	INT;

SELECT @RC = COUNT(*) FROM @TableA;

WITH Base (N)
AS
(
	SELECT	N
	--max value the number needed to of values to cross join to ensure enough rows
	FROM	dbo.GetNums(1,CEILING(CAST(@Top AS DECIMAL(18,5)) / @RC))
)

SELECT	TOP (@Top) A.VAL
FROM	@TableA AS A
CROSS
JOIN	Base AS B
ORDER	BY NEWID() ASC

What I've done is find out the count of rows from your source table and then calculate how to increase this count to satisfy your top requirement. A number table is then created using this calculated figure and you use a CARTESIAN JOIN (CROSS JOIN) to the source table.

Number/Tally table is a useful tool, suggest you look at some articles on its other uses The "Numbers" or "Tally" Table: What it is and how it replaces a loop

3

haha @Dohsan. yes i have some interesting threads. i love sql but for so many years the level of knowledge required was at the same level, now my new project has taken a step up, hence the threads.

really appreciate this.

will give it a go and update you

4

I see! You can also look to replace the number table function with this for the time being:

WITH Base (N)
AS
(
SELECT TOP (CAST(CEILING(CAST(@Top AS DECIMAL(18,5)) / @RC)AS INT)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS SY
)