SQL Stored Procedure INSERT with random records from a 2nd table [modified]
-
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_Campaigns (table to be inserted to)
Key | field_1 | field_2 | field_3 | field_4 | field_5 etc
Tbl_Organisations (table to get the 5 random records from)
Key | field_1
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.
modified on Tuesday, July 7, 2009 6:04 AM
-
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_Campaigns (table to be inserted to)
Key | field_1 | field_2 | field_3 | field_4 | field_5 etc
Tbl_Organisations (table to get the 5 random records from)
Key | field_1
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.
modified on Tuesday, July 7, 2009 6:04 AM
This may be useful....
select top 5 key, field_1 from tbl_organisations order by 100 * rand()
However, I haven't tested it cos I don't have SQL Server here so it may not work at all! :laugh:
-
This may be useful....
select top 5 key, field_1 from tbl_organisations order by 100 * rand()
However, I haven't tested it cos I don't have SQL Server here so it may not work at all! :laugh:
Hi, I would get a random record using the following:
SELECT TOP 5 (*) from tbl_organisations AS randomOrgs
However my issue is with how i would then catch these 5 records and insert them into the other table as a single record with the SP inputs.
-
Hi, I would get a random record using the following:
SELECT TOP 5 (*) from tbl_organisations AS randomOrgs
However my issue is with how i would then catch these 5 records and insert them into the other table as a single record with the SP inputs.
-
SELECT TOP 5 (*) from tbl_organisations order by newid()
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
Blue_boy, Please read the original post in this thread. Thanks for your help but you appear to have misinterpretated my question.