StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
1137321
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
7
CommunityOwnedDate
CreationDate
2009-07-16T12:45:04.063
FavoriteCount
0
LastActivityDate
2009-07-16T16:11:35.333
LastEditDate
2009-07-16T16:11:35.333
LastEditorUserId
65223
OwnerUserId
65223
ParentId
1137218
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
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 <a href="http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/" rel="nofollow noreferrer">here</a>, try this: <pre><code>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 </code></pre> EDIT based on need to use row_number() try this code: <pre><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 </code></pre> here is the output <pre><code>(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) </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. PORandom Records & SQL2005 Stored Procedure Insert
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USKM.
UserOwnerUserId
1. USKM.
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. PORandom Records & SQL2005 Stored Procedure Insert
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.