Random Records & SQL2005 Stored Procedure Insert

Question

Hi,

I need to submit data from a form to an SQL2005 database via a stored procedure. The difficult part is that i also need to get 5 random records from a secondary table and insert these as part of the inserted record in table 1.

My structure is akin to this:

---

Tbl_Organisations (table to get the 5 random records from)

Key | organisation_name |

---

Tbl_Campaigns (table to be inserted to)

Key | name | date | organisation_1 | organisation_2 | organisation_3 | etc........

---

I need to get 5 unique / random records from 'tbl_Organisations', catch them and insert them into a single record along with the data input to the stored procedure. I understand arrays arent an option in SQL2005(?).

So how do i catch these records and then insert them as a single record along with the SP Inputs?

Any help would be greatly appreciated as i am trying my best to get to grips with the complexities of SQL

Thanks.

Answer 1

Random records can be pulled by adding ORDER BY NEWID() at the end of the query to the 2nd table. Of course, use TOP 5 at the beginning to only return 5 rows.

Then use an SQL cursor loop through the returned rows to fill 5 static variables (@var1, @var2, etc.) that will be used in the final insert to the 1st table. You will have to use a loop counter and some CASE statements to figure out which variable to fill each run through the loop. Finally, make sure you handle the case where less than 5 rows are returned, if that's possible with your data set.

Answer 2

this should work if you have a sequential numeric key (like an identity) and never delete any rows or rollback any transactions. You don't post many details so this is just a guess, that you can base your code on. If you delete rows, you can use ROW_NUMBER() to make a similar approach work.

from here, try this:

DECLARE @Random1 INT;
DECLARE @Random2 INT;
DECLARE @Random3 INT;
DECLARE @Random4 INT;
DECLARE @Random5 INT;
DECLARE @Upper INT;
DECLARE @Lower INT

---- This will create a random number between 1 and count() of table
SET @Lower = 1 ---- The lowest random number
SELECT @Upper= COUNT(*) FROM Tbl_Organisations ---- The highest random number
SELECT @Random1 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random2 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random3 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random4 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random5 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

INSERT INTO Tbl_Campaigns (...., organisation_1,organisation_2,organisation_3...)
SELECT
    ...., t1.organisation_name, t2.organisation_name,t3.organisation_name...
    FROM Tbl_Organisations            t1
        INNER JOIN  Tbl_Organisations t2 ON t2.key=@Random2
        INNER JOIN  Tbl_Organisations t3 ON t3.key=@Random3
        INNER JOIN  Tbl_Organisations t4 ON t4.key=@Random4
        INNER JOIN  Tbl_Organisations t5 ON t5.key=@Random5
    WHERE t1.key=@Random1

EDIT based on need to use row_number()

try this code:

--set up tables
DECLARE @Tbl_Organisations table (O_KeyID int not null primary key identity(1,1), organisation_name varchar(20) not null)
DECLARE @Tbl_Campaigns table (C_KeyID int not null primary key identity(1,1), Cname varchar(10), createdate datetime, organisation_1 int, organisation_2 int, organisation_3 int, organisation_4 int, organisation_5 int)

--set up data
INSERT INTO @Tbl_Organisations VALUES ('one')
INSERT INTO @Tbl_Organisations VALUES ('two')
INSERT INTO @Tbl_Organisations VALUES ('three')
INSERT INTO @Tbl_Organisations VALUES ('pine')
INSERT INTO @Tbl_Organisations VALUES ('oak')
INSERT INTO @Tbl_Organisations VALUES ('maple')
INSERT INTO @Tbl_Organisations VALUES ('car')
INSERT INTO @Tbl_Organisations VALUES ('train')
INSERT INTO @Tbl_Organisations VALUES ('boat')
INSERT INTO @Tbl_Organisations VALUES ('dog')
INSERT INTO @Tbl_Organisations VALUES ('cat')
INSERT INTO @Tbl_Organisations VALUES ('horse')
INSERT INTO @Tbl_Organisations VALUES ('square')
INSERT INTO @Tbl_Organisations VALUES ('triangle')
INSERT INTO @Tbl_Organisations VALUES ('circle')

--temp areas to hold the 5 random numbers
DECLARE @Random1 INT;
DECLARE @Random2 INT;
DECLARE @Random3 INT;
DECLARE @Random4 INT;
DECLARE @Random5 INT;

--temp areas to hold the ranges to generate random numbers within
DECLARE @Upper INT;
DECLARE @Lower INT
DECLARE @range int

--get values to determine ranges of random numbers,
SET @Lower = 1 ---- The lowest random number
SELECT @Upper= COUNT(*) FROM @Tbl_Organisations ---- The highest random number
SET @Range=(@Upper - @Lower)/5


--will divide the @Tbl_Organisations table in to 5 ranges and select a random number from each range
--set the lower and upper limit on range 1
SELECT @Upper=@Lower+@Range
PRINT '@Lower='+CONVERT(varchar(50),@Lower)+', @Upper='+CONVERT(varchar(50),@Upper)
--get a random value from range 1
SELECT @Random1 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

--set the lower and upper limit on range 2
SELECT @Lower=@Upper+1,@Upper=@Lower+@Range
PRINT '@Lower='+CONVERT(varchar(50),@Lower)+', @Upper='+CONVERT(varchar(50),@Upper)
--get a random value from range 2
SELECT @Random2 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

--set the lower and upper limit on range 3
SELECT @Lower=@Upper+1,@Upper=@Lower+@Range
PRINT '@Lower='+CONVERT(varchar(50),@Lower)+', @Upper='+CONVERT(varchar(50),@Upper)
--get a random value from range 3
SELECT @Random3 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

--set the lower and upper limit on range 4
SELECT @Lower=@Upper+1,@Upper=@Lower+@Range
PRINT '@Lower='+CONVERT(varchar(50),@Lower)+', @Upper='+CONVERT(varchar(50),@Upper)
--get a random value from range 4
SELECT @Random4 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

--set the lower and upper limit on range 5
SELECT @Lower=@Upper+1,@Upper=COUNT(*) FROM @Tbl_Organisations
PRINT '@Lower='+CONVERT(varchar(50),@Lower)+', @Upper='+CONVERT(varchar(50),@Upper)
--get a random value from range 5
SELECT @Random5 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)


--this uses a CTE names "RowNumbers" to enumerate @Tbl_Organisations with a column "RowNumber" that is a unique sequential continueous from 1 to count(*) of @Tbl_Organisations
;with RowNumbers AS
(
    SELECT O_KeyID, row_number() over(order by O_KeyID) AS RowNumber from @Tbl_Organisations
)
--one row will be inserted, using the 5 joined in @Tbl_Organisations
INSERT INTO @Tbl_Campaigns (Cname,createdate, organisation_1, organisation_2, organisation_3, organisation_4, organisation_5)
--this will select and combine the 5 rows into 1 row, using the rownumber joined to the variables with the random values
SELECT
    'YourName'
        ,getdate()
        ,t1.O_KeyID --could be t1.organisation_name
        ,t2.O_KeyID --could be t2.organisation_name
        ,t3.O_KeyID --could be t3.organisation_name
        ,t4.O_KeyID --could be t4.organisation_name
        ,t5.O_KeyID --could be t5.organisation_name
    from @Tbl_Organisations           t1
        inner join @Tbl_Organisations t2 on t2.O_KeyID=@Random2
        inner join @Tbl_Organisations t3 on t3.O_KeyID=@Random3
        inner join @Tbl_Organisations t4 on t4.O_KeyID=@Random4
        inner join @Tbl_Organisations t5 on t5.O_KeyID=@Random5
    WHERE t1.O_KeyID=@Random1

--this shows the one row that was inserted
SELECT 'all unique',* FROM @Tbl_Campaigns

here is the output

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
@Lower=1, @Upper=3
@Lower=4, @Upper=6
@Lower=7, @Upper=9
@Lower=10, @Upper=12
@Lower=13, @Upper=15

(1 row(s) affected)
           C_KeyID     Cname      createdate              organisation_1 organisation_2 organisation_3 organisation_4 organisation_5
---------- ----------- ---------- ----------------------- -------------- -------------- -------------- -------------- --------------
all unique 1           YourName   2009-07-16 12:14:04.590 2              4              7              10             14

(1 row(s) affected)