Repository navigation
Expand file tree
/
Copy pathparse_table.sql
More file actions
154 lines (129 loc) · 6.55 KB
/
Copy pathparse_table.sql
File metadata and controls
154 lines (129 loc) · 6.55 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
USE [sd_airport_db]
GO
/****** Object: StoredProcedure [dbo].[dbo_parse_flat_table] Script Date: 25-07-2017 23:41:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[dbo_parse_flat_table]
as
begin
delete from Arrival
delete from Departure
delete from Airline
delete from Plane
delete from flight
--declare variables
declare @flight_num numeric(18,0),
@pilot_id numeric(18,0),
@airline_carrier varchar(50),
@year numeric(18,0),
@carry_on_bag_fee numeric(18,0),
@terminal int,
@arrival_time time(7),
@arrival_city varchar(50),
@departure_time time(7),
@departure_city varchar(50),
@plane_manufacturer varchar(50),
@material varchar(50),
@num_of_seats numeric(18,0),
@model_num numeric(18,0)
declare @id int
declare curs1 cursor
for
select
flight_num,
pilot_id,
airline_carrier,
year,
carry_on_bag_fee,
terminal,
arrival_time,
arrival_city,
departure_time,
departure_city,
plane_manufacturer,
material,
num_of_seats,
model_num
from [sd_airport_flat_table]
open curs1
fetch next from curs1 into @flight_num, @pilot_id, @airline_carrier,
@year, @carry_on_bag_fee, @terminal, @arrival_time,
@arrival_city, @departure_time, @departure_city,
@plane_manufacturer, @material, @num_of_seats, @model_num
while (@@FETCH_STATUS = 0)
begin
--inserting flight
if(not exists(select * from flight where flight_num=@flight_num))
begin
--print @flight_num
--print @pilot_id
--print @airline_carrier
--print @year
exec insert_flight @flight_num, @pilot_id, @airline_carrier, @year
end
--inserting airline
select
@id = null
select
@id = id
from
Airline
where
flight_num = @flight_num
AND airline_carrier = @airline_carrier
if (@id is null)
begin
exec insert_airline @flight_num, @pilot_id, @airline_carrier, @terminal, @carry_on_bag_fee
end
--inserting plane
select
@id = null
select
@id = id
from
Plane
where
flight_num = @flight_num
AND model_num = @model_num
if (@id is null)
begin
exec insert_plane @flight_num, @plane_manufacturer, @material, @num_of_seats, @model_num
end
--inserting arrival
select
@id = null
select
@id = id
from
Arrival
where
flight_num = @flight_num
AND arrival_time = @arrival_time
AND arrival_city = @arrival_city
if (@id is null)
begin
exec insert_arrival @arrival_time, @arrival_city, @flight_num
end
--inserting departure
select
@id = null
select
@id = id
from
Departure
where
flight_num = @flight_num
AND departure_time = @departure_time
AND departure_city = @departure_city
if (@id is null)
begin
exec insert_departure @departure_time, @departure_city, @flight_num
end
fetch next from curs1 into @flight_num, @pilot_id, @airline_carrier, @year, @carry_on_bag_fee, @terminal, @arrival_time, @arrival_city, @departure_time, @departure_city, @plane_manufacturer, @material, @num_of_seats, @model_num
end
--deallocate curs1
close curs1
deallocate curs1
end