vbWeMKZG

1declare @guests table(
2  Id int identity (1,1) primary key ,
3  Name varchar(20)
4)
5
6declare @countries table(
7  Id int identity (1,1) primary key ,
8  Name varchar(20)
9)
10
11declare @roomTypes table(
12  Id int identity (1,1) primary key ,
13  Name varchar(20)
14)
15
16insert into @guests(Name) values('Alexander'), ('Sofia'), ('Nikoletta'), ('Maria'), ('John')
17insert into @roomTypes(Name) values('Standard double'), ('Standard single'), ('Luxury double'), ('Family4')
18insert into @countries(Name) values('China'), ('Italy'), ('Spain'), ('Russia'), ('Poland'), ('Austria'), ('Vietnam')
19
20select max(Id) from @countries
21
22declare @room_type_count int = (select min(Id) from @roomTypes);
23declare @room_count int = 0; 
24
25while @room_type_count < (select max(Id) from @roomTypes)
26begin
27
28  insert into [2019_BBI18UNKNOWN_UNKNOWN_hwhotel].[dbo].[RoomType] (rtName, nightPrice) values((select Name from @roomTypes where Id = @room_type_count), round(rand()*120,0))
29
30  while @room_count < 15
31
32  begin
33  insert into [2019_BBI18UNKNOWN_UNKNOWN_hwhotel].[dbo].[Room] (tid, Number, s, seaView) values((select max(tid) from [2019_BBI18UNKNOWN_UNKNOWN_hwhotel].[dbo].[RoomType]), str(round(rand()*1000, 0)), round(rand()*100, 0), round(rand(), 0))
34  set @room_count = @room_count + 1
35  end
36
37  set @room_type_count = @room_type_count + 1
38  set @room_count = 0
39end
40
41
42set @room_count = (select min(rid) from [2019_BBI18UNKNOWN_UNKNOWN_hwhotel].[dbo].[Room]);
43declare @stay_count int = 0;
44declare @guest_count int = (select min(Id) from @guests);
45declare @name_count int = 0;
46declare @current_date date;
47declare @stay_duration int;
48declare @is_finished int;
49
50declare @countries_count int = (select min(Id) from @countries);
51
52while @guest_count < (select max(Id) from @guests)
53begin
54select @guest_count as guestcount
55  while @countries_count < (select max(Id) from @countries)
56  select @countries_count as countriescount
57  begin
58    insert into [2019_BBI18UNKNOWN_UNKNOWN_hwhotel].[dbo].[Guest] (country, gName, email) values(
59    (select Name from @countries where Id = @countries_count),
60    (select Name from @guests where Id = @guest_count),
61    concat((select Name from @guests where Id = @guest_count), @name_count, '_', (select Name from @countries where Id = @countries_count), '@gmail.com'))
62  set @countries_count = @countries_count + 1 
63  end
64set @guest_count = @guest_count + 1
65end
66
67set @guest_count = (select min(gid) from [2019_BBI18UNKNOWN_UNKNOWN_hwhotel].[dbo].[Guest]);
68
69while @room_count < (select max(rid) from [2019_BBI18UNKNOWN_UNKNOWN_hwhotel].[dbo].[Room])
70begin
71select @room_count as room 
72  while @guest_count < (select max(gid) from [2019_BBI18UNKNOWN_UNKNOWN_hwhotel].[dbo].[Guest])
73    begin
74    select @guest_count as guest
75    while @stay_count < 4
76      begin
77      select @stay_count as stay
78      set @current_date=concat('2019-09-', str(round(rand()*30 + 1, 0)))
79      set @stay_duration = round(rand()*14 + 1, 0)
80      insert into [2019_BBI18UNKNOWN_UNKNOWN_hwhotel].[dbo].[Stay] (rid, gid, start, nights, isFinished) values(@room_count, @guest_count, @current_date, @stay_duration, IIF(DATEADD(day, @stay_duration, @current_date) >= getdate(), 0, 1))
81      --select @room_count, @guest_count, @current_date, @stay_duration, IIF(DATEADD(day, @stay_duration, @current_date) >= getdate(), 0, 1)
82      set @stay_count = @stay_count + 1
83      end
84    set @guest_count = @guest_count + 1
85    end
86  set @room_count = @room_count + 1
87end