Случайное заполнение внешнего ключа в образце набора данных

Я создаю тестовые данные для новой базы данных, и у меня возникают проблемы с заполнением одного из полей внешнего ключа. Мне нужно создать относительно большое количество (1000) записей в таблице (SurveyResponses), которая имеет внешний ключ для таблицы только с 6 записями (Surveys)

В базе данных уже есть таблица Schools с несколькими тысячами записей. Ради аргументов скажем, что это выглядит так

Schools
+----+-------------+
| Id | School Name |
+----+-------------+
|  1 | PS 1        |
|  2 | PS 2        |
|  3 | PS 3        |
|  4 | PS 4        |
|  5 | PS 5        |
+----+-------------+

Я создаю новую таблицу Survey. Всего будет около 3 рядов.

Survey
+----+-------------+
| Id |    Col2     |
+----+-------------+
|  1 | 2014 Survey |
|  2 | 2015 Survey |
|  3 | 2016 Survey |
+----+-------------+

SurveyResponses просто привязывает школу к опросу.

Survey Responses
+----+----------+----------+
| Id | SchoolId | SurveyId |
+----+----------+----------+
|  1 |        1 |        1 |
|  2 |        2 |        2 |
|  3 |        3 |        1 |
|  4 |        4 |        3 |
|  5 |        5 |        2 |
+----+----------+----------+

Заполнение поля SurveyId доставляет мне больше всего хлопот. Я могу случайным образом выбрать 1000 школ, но я не нашел способа сгенерировать 1000 случайных идентификаторов SurveyId. Я пытался избежать цикла while, но, может быть, это единственный вариант?

Я использовал Red Gate SQL Data Generator для генерации некоторых тестовых данных, но в этом случае мне очень хотелось бы понять, как это можно сделать с помощью необработанного SQL.


sql sql-server data-generation test-data sample-data
person pnewhook    schedule 14.03.2015    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вот один из способов использования коррелированного подзапроса для получения случайного опроса, связанного с каждой школой:

select s.schoolid,
       (select top 1 surveyid
        from surveys
        order by newid()
       ) as surveyid
from schools s;

Примечание. Похоже, это не работает. Вот скрипт SQL, показывающий неработоспособность. Я очень удивлен, что это не работает, потому что newid() должно быть

РЕДАКТИРОВАТЬ:

Если вы знаете, что в идентификаторах опросов нет пробелов и они начинаются с 1, вы можете сделать следующее:

select 1 + abs(checksum(newid()) % 3) as surveyid

Я проверил, что это работает.

РЕДАКТИРОВАТЬ II:

Это кажется чрезмерно агрессивной оптимизацией (на мой взгляд). Корреляция запроса, по-видимому, решает проблему. Итак, что-то вроде этого должно работать:

select s.schoolid,
       (select top 1 surveyid
        from surveys s2
        where s2.surveyid = s.schoolid or s2.surveyid <> s.schoolid -- nonsensical condition to prevent over optimization
        order by newid()
       ) as surveyid
from schools s;

Вот скрипт SQL, демонстрирующий это.

person Gordon Linoff    schedule 14.03.2015
comment
Я пробовал это, но все опросы были одинаковыми в результате - person pnewhook; 14.03.2015
comment
@pnewhook . . . Это довольно странно. Я предлагаю другое решение, которое я считаю гораздо менее элегантным. - person Gordon Linoff; 14.03.2015
comment
@GordonLinoff У меня очень похожий вопрос, и я не хочу публиковать новый вопрос. В) Из двух таблиц с отношением PK, FK - как я могу очень просто выбрать 5 лучших из BedSizeTable справочной таблицы и заполнить король/ферзь/одиночный текст во вторичной HotelRoomsTable? Я хотел бы превратить это в хранилище процедур, если это возможно. - person aggie; 08.01.2016
comment
@эгги. . . Надлежащий способ задать вопрос - как вопрос, а не в комментарии. - person Gordon Linoff; 08.01.2016
comment
@GordonLinoff Я боюсь, что SO polecee застрелит его как лишнее :), но если вы ответите, я опубликую это: D - person aggie; 09.01.2016