SQLServerAirport/parse_customer_proc.sql at master · harshp8l/SQLServerAirport · GitHub

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
USE [sd_live_database]
GO
/****** Object:  StoredProcedure [dbo].[parse_customer]    Script Date: 25-07-2017 23:33:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[parse_customer]
AS
BEGIN
	delete from Customer

	declare @customer_id int,
			@id int,
			@first_name varchar(50),
			@last_name varchar(50),
			@address varchar(100),
			@city varchar(50),
			@state varchar(50),
			@zip int,
			@age int,
			@gender char(10),
			@hobby varchar(100),
			@income int,
			@children  int,
			@degree varchar(50),
			@own_house int

	declare curs1 cursor
	for
	select
			first_name,
			last_name,
			address,
			city,
			state,
			zip,
			age,
			gender,
			hobby,
			income,
			children,
			degree,
			ownHouse,
			id

	from [flat_customer]

	open curs1
		fetch next from curs1 into @first_name,@last_name,@address,@city,
								   @state,@zip, @age, @gender,@hobby, @income,
								   @children, @degree, @own_house, @id

		while (@@FETCH_STATUS = 0)
		begin
			if(not exists(select * from Customer where @customer_id=customer_id))
			begin
				insert Customer
				(
					customer_id,
					first_name,
					last_name,
					address,
					city,
					state,
					zip,
					age,
					gender,
					hobby,
					income,
					children,
					degree,
					own_house
				)

				select
					@customer_id,
					@first_name,
					@last_name,
					@address,
					@city,
					@state,
					@zip,
					@age,
					@gender,
					@hobby,
					@income,
					@children,
					@degree,
					@own_house
			end

			fetch next from curs1 into @first_name,@last_name,@address,
									   @city,@state,@zip, @age, @gender,
									   @hobby, @income, @children, @degree,
									   @own_house, @id
		end

	--deallocate curs1
	close curs1
	deallocate curs1
END