· 7 years ago · Nov 26, 2018, 09:26 PM
1use[bakery]
2
3IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Components_with_cursor'))
4DROP TABLE Components_with_cursor
5
6IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Characteristics_with_cursor'))
7DROP TABLE Characteristics_with_cursor
8
9CREATE TABLE Components_with_cursor(
10 СomponentID int PRIMARY KEY,
11 ComponentName nvarchar(50),
12 Amount INT,
13 ExpirationDate datetime,
14 CharacteristicsID INT,
15 Price money
16)
17
18CREATE TABLE Characteristics_with_cursor(
19 CharacteristicsID int PRIMARY KEY,
20 CharacteristicsName nvarchar(50),
21 CharacteristicsValue nvarchar(200),
22CharacteristicsDescription nvarchar(50)
23)
24
25DECLARE
26 @ComponentName nvarchar(50),
27 @Amount INT,
28 @ExpirationDate datetime,
29 @Price money,
30 @CharacteristicsName nvarchar(50),
31 @CharacteristicsValue nvarchar(200),
32 @CharacteristicsDescription nvarchar(50)
33
34DECLARE
35 @newСomponentID int ,
36 @newCharacteristicsID int,
37 @buf nvarchar(60)
38
39DECLARE @temp_Components_with_cursor Table(
40 СomponentID int PRIMARY KEY,
41 ComponentName nvarchar(50),
42 Amount INT,
43 ExpirationDate datetime,
44 CharacteristicsID INT ,
45 Price money
46)
47
48DECLARE @temp_Characteristics_with_cursor Table(
49 CharacteristicsID int PRIMARY KEY,
50 CharacteristicsName nvarchar(50),
51 CharacteristicsValue nvarchar(200),
52 CharacteristicsDescription nvarchar(50)
53 )
54
55
56
57SELECT @newСomponentID = MAX(СomponentID) + 1
58FROM Components
59SELECT @newCharacteristicsID = MAX(CharacteristicsID) + 1
60FROM Characteristics
61DECLARE TransCursor CURSOR FOR
62SELECT
63 ComponentName ,
64 Amount ,
65 ExpirationDate,
66 Price ,
67 Name,
68 Value,
69 Description
70FROM Components
71full join Characteristics
72on Components.CharacteristicsID=Characteristics.CharacteristicsID
73
74
75OPEN TransCursor
76SET @buf = 0
77FETCH NEXT FROM TransCursor
78INTO
79@ComponentName,
80 @Amount ,
81 @ExpirationDate ,
82 @Price ,
83 @CharacteristicsName ,
84 @CharacteristicsValue ,
85 @CharacteristicsDescription
86
87WHILE @@FETCH_STATUS = 0 BEGIN
88
89 IF 0 = (SELECT COUNT(ComponentName ) FROM @temp_Components_with_cursor WHERE ComponentName = @ComponentName ) AND @ComponentName IS NOT NULL
90 BEGIN
91 INSERT INTO @temp_Components_with_cursor(
92 СomponentID ,
93 ComponentName,
94 Amount ,
95 ExpirationDate ,
96 Price
97 )
98 VALUES (
99 @newСomponentID ,
100 @ComponentName,
101 @Amount ,
102 @ExpirationDate ,
103 @Price
104 )
105 SET @newСomponentID =@newСomponentID + 1
106 END
107
108 IF 0 = (SELECT COUNT(CharacteristicsName ) FROM @temp_Characteristics_with_cursor WHERE CharacteristicsName = @CharacteristicsName) AND @CharacteristicsName IS NOT NULL
109 BEGIN
110 INSERT INTO @temp_Characteristics_with_cursor(
111 CharacteristicsID ,
112 CharacteristicsName ,
113 CharacteristicsValue,
114 CharacteristicsDescription
115 )
116 VALUES (
117 @newCharacteristicsID,
118 @CharacteristicsName ,
119 @CharacteristicsValue ,
120 @CharacteristicsDescription
121 )
122 SET @newCharacteristicsID = @newCharacteristicsID + 1
123 END
124 FETCH NEXT FROM TransCursor
125 INTO
126 @ComponentName,
127 @Amount ,
128 @ExpirationDate ,
129 @Price,
130 @CharacteristicsName ,
131 @CharacteristicsValue ,
132 @CharacteristicsDescription
133 END
134CLOSE TransCursor
135DEALLOCATE TransCursor
136INSERT INTO Components_with_cursor (
137 СomponentID ,
138 ComponentName,
139 Amount ,
140 ExpirationDate ,
141 Price
142)SELECT
143 СomponentID ,
144 ComponentName,
145 Amount ,
146 ExpirationDate ,
147 Price
148 FROM @temp_Components_with_cursor
149INSERT INTO Characteristics_with_cursor (
150 CharacteristicsID ,
151 CharacteristicsName ,
152 CharacteristicsValue,
153 CharacteristicsDescription
154)SELECT
155 CharacteristicsID ,
156 CharacteristicsName ,
157 CharacteristicsValue,
158 CharacteristicsDescription
159 FROM @temp_Characteristics_with_cursor