· 10 years ago · Aug 22, 2016, 11:48 AM
1USE [TestDB]
2GO
3/****** Object: Table [dbo].[table_PMT15B_FinalState_GU_test] Script Date: 8/10/2016 7:38:43 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8SET ANSI_PADDING ON
9GO
10CREATE TABLE [dbo].[table_PMT15B_FinalState_GU_test](
11[PMT_ID] [int] NOT NULL,
12[OBJECTID] [float] NULL,
13[Status] [nvarchar](255) NULL,
14[Addr_type] [nvarchar](255) NULL,
15[X] [float] NULL,
16[Y] [float] NULL,
17[fcs_id] [float] NULL,
18[epa_id] [varchar](1000) NULL,
19[prop_name] [nvarchar](4000) NULL,
20[prop_addr1] [nvarchar](4000) NULL,
21[prop_addr2] [nvarchar](4000) NULL,
22[prop_city] [nvarchar](255) NULL,
23[prop_county] [nvarchar](255) NULL,
24[prop_state] [nvarchar](255) NULL,
25[prop_country] [nvarchar](255) NULL,
26[prop_zip] [nvarchar](255) NULL,
27[prop_zip4] [nvarchar](255) NULL,
28[X_meters] [nvarchar](255) NULL,
29[Y_meters] [nvarchar](255) NULL,
30[Classification_Codes] [nvarchar](255) NULL,
31[Classification_Codes_2] [nvarchar](255) NULL,
32[date_geocoded] [datetime] NULL,
33[linked_pmt_id] [int] NULL,
34[looptag] [nvarchar](50) NULL
35) ON [PRIMARY]
36
37GO
38SET ANSI_PADDING OFF
39GO
40INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (451678, 433393, N'U', NULL, NULL, NULL, 1000208, N'GU5170090018', N'BARRIGADA VILLAGE ABNDONED DUMP', N'NAVAL COMMUNICATIONS STA', NULL, N'BARRIGADA', N'GUAM', N'GU', N'GUM', N'96913', NULL, N'1.#QNAN ', N'1.#QNAN ', N'CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES', N',CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES,', NULL, 451678, N'2Ug-NBE')
41INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (451702, 433417, N'U', NULL, NULL, NULL, 1000232, N'GU6170090025', N'CAMP COVINGTON', N'APRA HARBOR NAVAL COMPLEX', NULL, N'PITI', N'GUAM', N'GU', N'GUM', N'96925', NULL, N'1.#QNAN ', N'1.#QNAN ', N'CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES', N',CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES,', NULL, 451678, N'2Ug-NBE')
42INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (451738, 433453, N'U', NULL, NULL, NULL, 1000268, N'GU8170090015', N'FINEGAYAN HOUSING ABANDONED DUMP', N'NAV COMM CTR SOUTH FINEGAYAN', NULL, N'AGANA', N'GUAM', N'GU', N'GUM', N'96932', NULL, N'1.#QNAN ', N'1.#QNAN ', N'CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES', N',CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES,', NULL, 451678, N'2Ug-NBE')
43INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (451850, 433565, N'U', NULL, NULL, NULL, 1000380, N'GU6170090009', N'NAVAL COMM AREA MASTER STA WEST PACIFIC', N'NAVCAMS WESTPAC', NULL, N'AGANA', N'GUAM', N'GU', N'GUM', N'96932', NULL, N'1.#QNAN ', N'1.#QNAN ', N'CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES', N',CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES,', NULL, 451678, N'2Ug-NBE')
44INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (451851, 433566, N'U', NULL, NULL, NULL, 1000381, N'GU2170090011', N'NAVAL FACIL GUAM', N'NAVAL FACILITY GUAM', NULL, N'AGANA', N'GUAM', N'GU', N'GUM', N'96910', NULL, N'1.#QNAN ', N'1.#QNAN ', N'CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES', N',CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES,', NULL, 451678, N'2Ug-NBE')
45INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (451858, 433573, N'U', NULL, NULL, NULL, 1000388, N'GU7170090024', N'NAVAL REGIONAL DENTAL CENTER', N'APRA HARBOR NAVAL COMPLEX', NULL, N'PITI', N'GUAM', N'GU', N'GUM', N'96925', NULL, N'1.#QNAN ', N'1.#QNAN ', N'CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES', N',CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES,', NULL, 451678, N'2Ug-NBE')
46INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (451861, 433576, N'U', NULL, NULL, NULL, 1000391, N'GU4170027334', N'NAVAL SHIP REPAIR FACILITY - GUAM', N'NAVAL FACILITY', NULL, N'AGANA', N'GUAM', N'GU', N'GUM', N'96910', NULL, N'1.#QNAN ', N'1.#QNAN ', N'CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES', N',CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES,', NULL, 451678, N'2Ug-NBE')
47INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (451862, 433577, N'U', NULL, NULL, NULL, 1000392, N'GU7170027323', N'NAVAL STA GUAM', N'NAVY PUBLIC WORKS CTR', NULL, N'AGANA', N'GUAM', N'GU', N'GUM', N'96910', NULL, N'1.#QNAN ', N'1.#QNAN ', N'CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES', N',CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES,', NULL, 451678, N'2Ug-NBE')
48INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (451866, 433581, N'U', NULL, NULL, NULL, 1000396, N'GU8170090023', N'NAVAL SUPPLY DEPOT', N'APRA HARBOR NAVAL COMPLEX', NULL, N'PITI', N'GUAM', N'GU', N'GUM', N'96925', NULL, N'1.#QNAN ', N'1.#QNAN ', N'CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES', N',CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES,', NULL, 451678, N'2Ug-NBE')
49INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (451873, 433588, N'U', NULL, NULL, NULL, 1000403, N'GU0170090005', N'NIMITZ HILL ANNEX', N'BORROW PIT DUMPSITE', NULL, N'AGANA', N'GUAM', N'GU', N'GUM', N'96910', NULL, N'1.#QNAN ', N'1.#QNAN ', N'CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES', N',CERCLIS,FEDERAL FACILITY,SEMS_8R_ACTIVE SITES,', NULL, 451678, N'2Ug-NBE')
50INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (459891, 440767, N'M', N'GIS LOCATION', 144.89736, 13.57291, 1017434, N'GU6571999519', N'ANDERSEN AIR FORCE BASE', N'PERIMETER RD', NULL, N'YIGO', N'GUAM', N'GU', N'GUM', N'96929', NULL, N'16129900.3322 ', N'1525262.6525 ', N'CERCLIS,CONSENT (DECREES),FED E C,FED I C,FEDERAL FACILITY,NPL,NPL EPA R9 GIS,ROD,SEMS_8R_ACTIVE SITES,SEMS_FINAL NPL', N',CERCLIS,CONSENT (DECREES),FED E C,FED I C,FEDERAL FACILITY,NPL,NPL EPA R9 GIS,ROD,SEMS_8R_ACTIVE SITES,SEMS_FINAL NPL,', NULL, 451678, N'2Ug-NBE')
51INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (459892, 440768, N'M', N'GIS LOCATION', -144.7474, 13.43916, 1017435, N'GUD980637649', N'ORDOT LANDFILL', N'SAN CARLOS STREET ORDOT', NULL, N'AGANA', N'GUAM', N'GU', N'GUM', N'96910', NULL, N'-16113206.8601 ', N'1509950.2077 ', N'CERCLIS,CONSENT (DECREES),FED E C,NPL,NPL EPA R9 GIS,ROD,SEMS_8R_ACTIVE SITES,SEMS_FINAL NPL', N',CERCLIS,CONSENT (DECREES),FED E C,NPL,NPL EPA R9 GIS,ROD,SEMS_8R_ACTIVE SITES,SEMS_FINAL NPL,', NULL, 451678, N'2Ug-NBE')
52INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (1318105, 254096, N'M', N'PointAddress', -85.678146, 40.105991, 2952782, NULL, N'ANDERSEN AIR FORCE BASE', N'1000 MAIN STREET', NULL, N'ANDERSEN', NULL, N'GU', N'GUM', N'96910', NULL, N'-9537647.5837 ', N'4881356.5772 ', N'DOCKET', N',DOCKET,', CAST(N'2016-03-17 20:03:03.067' AS DateTime), 1318105, N'1Ma-NBCNBZ')
53INSERT [dbo].[table_PMT15B_FinalState_GU_test] ([PMT_ID], [OBJECTID], [Status], [Addr_type], [X], [Y], [fcs_id], [epa_id], [prop_name], [prop_addr1], [prop_addr2], [prop_city], [prop_county], [prop_state], [prop_country], [prop_zip], [prop_zip4], [X_meters], [Y_meters], [Classification_Codes], [Classification_Codes_2], [date_geocoded], [linked_pmt_id], [looptag]) VALUES (1318447, 296713, N'U', NULL, NULL, NULL, 2953124, NULL, N'SHELL GUAM AGAT TERMINAL', N'CHALAN TENJO VISTA', NULL, N'PITI', NULL, N'GU', N'GUM', N'96915', NULL, N'1.#QNAN ', N'1.#QNAN ', N'DOCKET', N',DOCKET,', NULL, NULL, NULL)
54
55--Mappable
56SET NOCOUNT ON
57
58USE TestDB
59
60--DROP SYNONYM tblname_MT2
61
62--IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[TestDB].[dbo].[table_PMT15B_FinalState_MS]'))
63
64 CREATE SYNONYM tblname_MT2 FOR [TestDB].[dbo].[table_PMT15B_FinalState_MT2]
65GO
66
67-- SELECT 'Processing 1Ma-NBCNBZ'
68DECLARE @pmtid int
69DECLARE @prop_name nvarchar(4000)
70DECLARE @prop_addr1 nvarchar(4000)
71DECLARE @prop_city nvarchar(4000)
72DECLARE @prop_zip nvarchar(4000)
73
74DECLARE tblName_cur1 CURSOR local forward_only static read_only
75FOR SELECT PMT_ID, prop_name, prop_addr1, prop_city, prop_zip
76FROM tblname_MT2 WITH (NOLOCK)
77 WHERE linked_pmt_id IS NULL
78 AND (status <> 'U' AND addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND addr_type IS NOT NULL)
79 AND prop_city IS NOT NULL
80 AND prop_zip IS NOT NULL
81 ORDER BY prop_name, prop_addr1, prop_city, prop_zip, pmt_id ASC
82
83OPEN tblName_cur1
84FETCH NEXT FROM tblName_cur1 INTO @pmtid, @prop_name, @prop_addr1, @prop_city, @prop_zip
85
86WHILE @@FETCH_STATUS = 0
87
88BEGIN
89
90 IF (@prop_name IS NULL or @prop_addr1 IS NULL)
91UPDATE tblname_MT2
92SET [linked_pmt_id] = @pmtid, looptag = '1Ma-NBCNBZ'
93WHERE linked_pmt_id IS NULL
94AND (Status <> 'U' AND ( addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND addr_type IS NOT NULL))
95AND (prop_city IS NOT NULL AND prop_city = @prop_city)
96AND (prop_zip IS NOT NULL AND prop_zip = @prop_zip)
97AND ISNULL(prop_addr1,'NULL') = ISNULL(@prop_addr1,'NULL')
98AND ISNULL(prop_name,'NULL') = ISNULL(@prop_name,'NULL')
99ELSE
100UPDATE tblname_MT2
101SET [linked_pmt_id] = @pmtid, looptag = '1Ma-NBCNBZ'
102WHERE linked_pmt_id IS NULL
103AND (Status <> 'U' AND ( addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND addr_type IS NOT NULL))
104AND (prop_city IS NOT NULL AND prop_city = @prop_city)
105AND (prop_zip IS NOT NULL AND prop_zip = @prop_zip)
106AND prop_addr1 = @prop_addr1
107AND prop_name = @prop_name
108
109FETCH NEXT FROM tblName_cur1 INTO @pmtid, @prop_name, @prop_addr1, @prop_city, @prop_zip
110
111END
112
113CLOSE tblName_cur1
114DEALLOCATE tblName_cur1
115GO
116--------------------------------------
117-- SELECT 'Processing 1Mb-NBCNBZ'
118UPDATE a
119SET a.linked_pmt_id = b.linked_pmt_id, a.looptag = '1Mb-NBCBZ'
120FROM tblname_MT2 a
121 JOIN (SELECT * FROM tblname_MT2 with (NOLOCK) WHERE pmt_id = linked_pmt_id) b
122ON ISNULL(a.prop_name,'NULL') = ISNULL(b.prop_name,'NULL') AND
123 ISNULL(a.prop_addr1,'NULL') = ISNULL(b.prop_addr1,'NULL') AND
124 a.prop_city = b.prop_city
125WHERE a.linked_pmt_id IS NULL AND
126(a.status <> 'U' AND a.addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND a.addr_type IS NOT NULL) AND
127a.prop_city IS NOT NULL AND
128a.prop_zip IS NULL
129GO
130--------------------------------------
131-- SELECT 'Processing 1Mc-NBCBZ'
132DECLARE @pmtid int
133DECLARE @prop_name nvarchar(4000)
134DECLARE @prop_addr1 nvarchar(4000)
135DECLARE @prop_city nvarchar(4000)
136DECLARE @prop_zip nvarchar(4000)
137
138DECLARE tblName_cur2 CURSOR local forward_only static read_only
139FOR SELECT PMT_ID, prop_name, prop_addr1, prop_city
140FROM tblname_MT2 WITH (NOLOCK)
141 WHERE linked_pmt_id IS NULL
142AND (STATUS <> 'U' AND (addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND addr_type IS NOT NULL ))
143AND prop_city IS NOT NULL
144 AND prop_zip IS NULL
145 ORDER BY prop_name, prop_addr1, prop_city, pmt_id ASC
146
147OPEN tblName_cur2
148FETCH NEXT FROM tblName_cur2 INTO @pmtid, @prop_name, @prop_addr1, @prop_city
149
150WHILE @@FETCH_STATUS = 0
151
152BEGIN
153
154IF (@prop_name IS NULL or @prop_addr1 IS NULL)
155UPDATE tblname_MT2
156SET [linked_pmt_id] = @pmtid, looptag = '1Mc-NBCBZ'
157WHERE linked_pmt_id IS NULL
158AND (Status <> 'U' AND (addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND addr_type IS NOT NULL))
159AND (prop_city IS NOT NULL AND prop_city = @prop_city )
160AND prop_zip IS NULL
161AND ISNULL(prop_addr1,'NULL') = ISNULL(@prop_addr1,'NULL')
162AND ISNULL(prop_name,'NULL') = ISNULL(@prop_name,'NULL')
163ELSE
164UPDATE tblname_MT2
165SET [linked_pmt_id] = @pmtid, looptag = '1Mc-NBCBZ'
166WHERE linked_pmt_id IS NULL
167AND (Status <> 'U' AND (addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND addr_type IS NOT NULL))
168AND (prop_city IS NOT NULL AND prop_city = @prop_city )
169AND prop_zip IS NULL
170AND prop_addr1 = @prop_addr1
171AND prop_name = @prop_name
172
173FETCH NEXT FROM tblName_cur2 INTO @pmtid, @prop_name, @prop_addr1, @prop_city
174
175END
176
177CLOSE tblName_cur2
178DEALLOCATE tblName_cur2
179GO
180
181--Unmappable
182SET NOCOUNT ON
183
184USE TestDB
185
186--DROP SYNONYM tblname1
187
188--IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[TestDB].[dbo].[table_PMT15B_FinalState_IN]'))
189
190 CREATE SYNONYM tblname1 FOR [TestDB].[dbo].[table_PMT15B_FinalState_GU1]
191GO
192
193
194DECLARE @pmtid int
195DECLARE @prop_name nvarchar(4000)
196DECLARE @prop_addr1 nvarchar(4000)
197DECLARE @prop_city nvarchar(255)
198DECLARE @prop_zip nvarchar(255)
199
200DECLARE tblName_cur1 CURSOR local forward_only static read_only
201FOR SELECT PMT_ID, prop_name, prop_addr1, prop_city, prop_zip FROM tblname1 WITH(NOLOCK)
202WHERE LINKED_PMT_ID IS NULL
203AND (STATUS = 'U' OR (status <> 'U' AND ADDR_TYPE IN ( 'GIS LOCATION','ASSIGNED LOCATION') OR addr_type IS NULL))
204AND (prop_addr1 IS NOT NULL AND prop_addr1 LIKE '[0-9]%')
205AND prop_city IS NOT NULL
206AND prop_zip IS NOT NULL
207
208OPEN tblName_cur1
209FETCH NEXT FROM tblName_cur1 INTO @pmtid, @prop_name, @prop_addr1, @prop_city, @prop_zip
210
211WHILE @@FETCH_STATUS = 0
212BEGIN
213
214 IF (@prop_name IS NULL)
215UPDATE tblname1
216SET [linked_pmt_id] = @pmtid, looptag = '2Ua-NBCNBZ'
217WHERE linked_pmt_id IS NULL
218AND (status = 'U' OR (status <> 'U' AND (addr_type IN ('GIS LOCATION','ASSIGNED LOCATION') OR addr_type IS NULL)))
219AND (prop_addr1 IS NOT NULL AND prop_addr1 LIKE '[0-9]%')
220AND prop_addr1 = @prop_addr1
221AND prop_name IS NULL
222AND (prop_city IS NOT NULL AND prop_city = @prop_city)
223AND (prop_zip IS NOT NULL AND prop_zip = @prop_zip)
224ELSE
225UPDATE tblname1
226SET [linked_pmt_id] = @pmtid, looptag = '2Ua-NBCNBZ'
227WHERE linked_pmt_id IS NULL
228AND (status = 'U' OR (status <> 'U' AND (addr_type IN ('GIS LOCATION','ASSIGNED LOCATION') OR addr_type IS NULL)))
229AND (prop_addr1 IS NOT NULL AND prop_addr1 LIKE '[0-9]%')
230AND prop_addr1 = @prop_addr1
231AND prop_name = @prop_name
232AND (prop_city IS NOT NULL AND prop_city = @prop_city)
233AND (prop_zip IS NOT NULL AND prop_zip = @prop_zip)
234
235FETCH NEXT FROM tblName_cur1 INTO @pmtid, @prop_name, @prop_addr1, @prop_city, @prop_zip
236END
237
238CLOSE tblName_cur1
239DEALLOCATE tblName_cur1
240GO
241----------------------------------------
242UPDATE a
243SET a.linked_pmt_id = b.linked_pmt_id, a.looptag = '2Ub-NBCBZ'
244FROM tblname1 a JOIN (SELECT * FROM tblname1 with (NOLOCK) WHERE pmt_id = linked_pmt_id) b
245ON ISNULL(a.prop_name,'NULL') = ISNULL(b.prop_name,'NULL')
246AND a.prop_addr1 = b.prop_addr1
247AND a.prop_city = b.prop_city
248WHERE a.linked_pmt_id IS NULL
249AND (a.status = 'U' OR (a.status <> 'U' AND (a.addr_type IN ('GIS LOCATION','ASSIGNED LOCATION') OR a.addr_type IS NULL)))
250AND (a.prop_addr1 IS NOT NULL AND a.prop_addr1 LIKE '[0-9]%')
251AND a.prop_city IS NOT NULL
252AND a.prop_zip IS NULL
253--ORDER BY b.linked_pmt_id,a.prop_addr1
254GO
255----------------------------------------
256DECLARE @pmtid int
257DECLARE @prop_name nvarchar(4000)
258DECLARE @prop_addr1 nvarchar(4000)
259DECLARE @prop_city nvarchar(255)
260DECLARE @prop_zip nvarchar(255)
261
262DECLARE tblName_cur2 CURSOR local forward_only static read_only
263FOR SELECT PMT_ID, prop_name, prop_addr1, prop_city FROM tblname1 WITH (NOLOCK)
264WHERE linked_pmt_id IS NULL
265AND (status = 'U' OR (status <> 'U' AND (addr_type IN ('GIS LOCATION','ASSIGNED LOCATION') OR addr_type IS NULL)))
266AND prop_addr1 IS NOT NULL AND prop_addr1 LIKE '[0-9]%'
267AND prop_city IS NOT NULL
268AND prop_zip IS NULL
269ORDER BY prop_name, prop_addr1, prop_city, pmt_id ASC
270
271OPEN tblName_cur2
272FETCH NEXT FROM tblName_cur2 INTO @pmtid, @prop_name, @prop_addr1, @prop_city
273
274WHILE @@FETCH_STATUS = 0
275BEGIN
276 IF (@prop_name IS NULL)
277UPDATE tblname1
278SET [linked_pmt_id] = @pmtid, looptag = '2Uc-NBCBZ'
279WHERE linked_pmt_id IS NULL
280AND (status = 'U' OR (status <> 'U' AND (addr_type IN ('GIS LOCATION','ASSIGNED LOCATION') OR addr_type IS NULL)))
281AND prop_addr1 IS NOT NULL AND prop_addr1 LIKE '[0-9]%'
282AND prop_addr1 = @prop_addr1
283AND prop_name IS NULL
284AND prop_city IS NOT NULL AND prop_city = @prop_city
285AND prop_zip IS NULL
286ELSE
287UPDATE tblname1
288SET [linked_pmt_id] = @pmtid, looptag = '2Uc-NBCBZ'
289WHERE linked_pmt_id IS NULL
290AND (status = 'U' OR (status <> 'U' AND (addr_type IN ('GIS LOCATION','ASSIGNED LOCATION') OR addr_type IS NULL)))
291 AND prop_addr1 IS NOT NULL AND prop_addr1 LIKE '[0-9]%'
292AND prop_addr1 = @prop_addr1
293AND prop_name = @prop_name
294AND prop_city IS NOT NULL AND prop_city = @prop_city
295AND prop_zip IS NULL
296
297FETCH NEXT FROM tblName_cur2 INTO @pmtid, @prop_name, @prop_addr1, @prop_city
298END
299
300CLOSE tblName_cur2
301DEALLOCATE tblName_cur2
302GO