· 8 years ago · May 22, 2018, 10:28 AM
1/**************************************************************************
2-- -Generated by xSQL SDK for Schema Comparison and Synchronization
3-- -Date/Time: January 12, 2012 19:57:58
4
5-- -Summary:
6 Contains the T-SQL script that makes the database
7 svr-nlpg01\sqlexpress.Howslow the same as the database svr-demo03.NLPG-HOUNSLOW
8
9-- -Action:
10 Execute this script on svr-nlpg01\sqlexpress.Howslow
11
12-- -SQL Server version: 10.0.1600
13**************************************************************************/
14
15
16/**************************************************************************
17Warnings:
18Table dbo.ADMIN_AREA_NAME will be altered with an intermediate table.
19Table dbo.ADTNLSTR will be altered with an intermediate table.
20Table dbo.ALIASINFO will be altered with an intermediate table.
21Table dbo.ALT_STREET_LPI will be altered with an intermediate table.
22Table dbo.BLPU will be altered with an intermediate table.
23Table dbo.BLPU_CANDIDATE will be altered with an intermediate table.
24Table dbo.BLPU_CLASS will be altered with an intermediate table.
25Table dbo.BLPU_CLASSIFICATION will be altered with an intermediate table.
26Table dbo.Candidate_TFI will be altered with an intermediate table.
27Table dbo.Candidate_TFI_ARCHIVE will be altered with an intermediate table.
28Table dbo.ESU will be altered with an intermediate table.
29Table dbo.EXPORT_DATES will be altered with an intermediate table.
30Table dbo.EXPORTDATES_TFIOLD will be altered with an intermediate table.
31Table dbo.HWW will be altered with an intermediate table.
32Table dbo.LAND_AND_PROPERTY_IDENTIFIER will be altered with an intermediate table.
33Table dbo.LOCALITY will be altered with an intermediate table.
34Table dbo.LPI_CANDIDATE will be altered with an intermediate table.
35Table dbo.NODE will be altered with an intermediate table.
36Table dbo.ORGANISATION will be altered with an intermediate table.
37Table dbo.REINSTDS will be altered with an intermediate table.
38Table dbo.SPCLDSG will be altered with an intermediate table.
39Table dbo.STREET_SP will be altered with an intermediate table.
40Table dbo.SUCCESSOR_XREF will be altered with an intermediate table.
41Table dbo.TFI will be altered with an intermediate table.
42Table dbo.TOWN will be altered with an intermediate table.
43Table dbo.TRANSFER_FILE_IMAGE will be altered with an intermediate table.
44Table dbo.TRANSFER_FILE_IMAGE_ARCHIVE will be altered with an intermediate table.
45**************************************************************************/
46
47
48BEGIN TRANSACTION
49 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
50 SET QUOTED_IDENTIFIER ON
51 SET ANSI_NULLS ON
52 SET ANSI_PADDING ON
53 SET ANSI_WARNINGS ON
54 SET ARITHABORT ON
55 SET NUMERIC_ROUNDABORT OFF
56 SET CONCAT_NULL_YIELDS_NULL ON
57 SET XACT_ABORT ON
58COMMIT TRANSACTION
59GO
60
61IF EXISTS (select * from tempdb..sysobjects where id = OBJECT_ID('tempdb..#ErrorLog'))
62 DROP TABLE #ErrorLog
63CREATE TABLE #ErrorLog
64( pkid [int] IDENTITY(1,1) NOT NULL, errno [int] NOT NULL, errdescr [varchar](100) NULL )
65GO
66
67IF @@TRANCOUNT=0 BEGIN TRANSACTION
68GO
69DROP TABLE [dbo].[ADDRESS_SEARCH]
70GO
71IF @@ERROR<>0
72Begin
73 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
74 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to drop table dbo.ADDRESS_SEARCH')
75END
76GO
77
78IF @@TRANCOUNT=0 BEGIN TRANSACTION
79GO
80SET ANSI_NULLS ON
81GO
82SET QUOTED_IDENTIFIER ON
83GO
84
85CREATE TABLE [dbo].[tmp_ADMIN_AREA_NAME] (
86 [AAN_REF] [int] IDENTITY (1,1) NOT NULL,
87 [AAN_DESCRIPTION] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
88 [TYPE] [varchar](1) COLLATE Latin1_General_CI_AS NULL
89) ON [PRIMARY]
90
91IF @@ERROR<>0
92Begin
93 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
94 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ADMIN_AREA_NAME')
95END
96GO
97SET IDENTITY_INSERT [dbo].[tmp_ADMIN_AREA_NAME] ON
98IF EXISTS (SELECT * FROM [dbo].[ADMIN_AREA_NAME])
99 EXEC
100 ('
101 INSERT INTO [dbo].[tmp_ADMIN_AREA_NAME] ([AAN_REF],[AAN_DESCRIPTION],[TYPE])
102 SELECT [AAN_REF],[AAN_DESCRIPTION],[TYPE] FROM [dbo].[ADMIN_AREA_NAME] TABLOCKX
103 ')
104IF @@ERROR<>0
105Begin
106 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
107 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ADMIN_AREA_NAME')
108END
109GO
110SET IDENTITY_INSERT [dbo].[tmp_ADMIN_AREA_NAME] OFF
111
112DROP TABLE [dbo].[ADMIN_AREA_NAME]
113GO
114IF @@ERROR<>0
115Begin
116 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
117 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ADMIN_AREA_NAME')
118END
119GO
120EXECUTE sp_rename N'[dbo].[tmp_ADMIN_AREA_NAME]', N'ADMIN_AREA_NAME', 'OBJECT'
121
122IF @@ERROR<>0
123Begin
124 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
125 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ADMIN_AREA_NAME')
126END
127GO
128
129IF @@TRANCOUNT=0 BEGIN TRANSACTION
130GO
131SET ANSI_NULLS ON
132GO
133SET QUOTED_IDENTIFIER ON
134GO
135
136CREATE TABLE [dbo].[tmp_ADTNLSTR] (
137 [APGRECNO] [bigint] IDENTITY (1,1) NOT NULL,
138 [UPDDATE] [datetime] NULL,
139 [USRN] [varchar](8) COLLATE Latin1_General_CI_AS NULL,
140 [SEQNUM] [numeric](3,0) NULL,
141 [RSCCODE] [varchar](250) COLLATE Latin1_General_CI_AS NULL,
142 [WHOLEROAD] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
143 [LOCTEXT] [varchar](250) COLLATE Latin1_General_CI_AS NULL,
144 [SWACODE] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
145 [GEOMETRY] [geometry] NULL
146) ON [PRIMARY]
147
148IF @@ERROR<>0
149Begin
150 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
151 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ADTNLSTR')
152END
153GO
154SET IDENTITY_INSERT [dbo].[tmp_ADTNLSTR] ON
155IF EXISTS (SELECT * FROM [dbo].[ADTNLSTR])
156 EXEC
157 ('
158 INSERT INTO [dbo].[tmp_ADTNLSTR] ([APGRECNO],[UPDDATE],[USRN],[SEQNUM],[RSCCODE],[WHOLEROAD],[LOCTEXT],[SWACODE],[GEOMETRY])
159 SELECT [APGRECNO],[UPDDATE],[USRN],[SEQNUM],[RSCCODE],[WHOLEROAD],[LOCTEXT],[SWACODE],[GEOMETRY] FROM [dbo].[ADTNLSTR] TABLOCKX
160 ')
161IF @@ERROR<>0
162Begin
163 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
164 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ADTNLSTR')
165END
166GO
167SET IDENTITY_INSERT [dbo].[tmp_ADTNLSTR] OFF
168
169DROP TABLE [dbo].[ADTNLSTR]
170GO
171IF @@ERROR<>0
172Begin
173 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
174 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ADTNLSTR')
175END
176GO
177EXECUTE sp_rename N'[dbo].[tmp_ADTNLSTR]', N'ADTNLSTR', 'OBJECT'
178
179IF @@ERROR<>0
180Begin
181 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
182 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ADTNLSTR')
183END
184GO
185
186IF @@TRANCOUNT=0 BEGIN TRANSACTION
187GO
188SET ANSI_NULLS ON
189GO
190SET QUOTED_IDENTIFIER ON
191GO
192
193CREATE TABLE [dbo].[tmp_ALIASINFO] (
194 [ALIAS_ID] [int] IDENTITY (1,1) NOT NULL,
195 [NAME] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
196 [ALIAS_NAME] [varchar](500) COLLATE Latin1_General_CI_AS NULL,
197 [ALIASTYPE] [varchar](4) COLLATE Latin1_General_CI_AS NULL
198) ON [PRIMARY]
199
200IF @@ERROR<>0
201Begin
202 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
203 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ALIASINFO')
204END
205GO
206SET IDENTITY_INSERT [dbo].[tmp_ALIASINFO] ON
207IF EXISTS (SELECT * FROM [dbo].[ALIASINFO])
208 EXEC
209 ('
210 INSERT INTO [dbo].[tmp_ALIASINFO] ([ALIAS_ID],[NAME],[ALIAS_NAME],[ALIASTYPE])
211 SELECT [ALIAS_ID],[NAME],[ALIAS_NAME],[ALIASTYPE] FROM [dbo].[ALIASINFO] TABLOCKX
212 ')
213IF @@ERROR<>0
214Begin
215 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
216 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ALIASINFO')
217END
218GO
219SET IDENTITY_INSERT [dbo].[tmp_ALIASINFO] OFF
220
221DROP TABLE [dbo].[ALIASINFO]
222GO
223IF @@ERROR<>0
224Begin
225 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
226 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ALIASINFO')
227END
228GO
229EXECUTE sp_rename N'[dbo].[tmp_ALIASINFO]', N'ALIASINFO', 'OBJECT'
230
231IF @@ERROR<>0
232Begin
233 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
234 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ALIASINFO')
235END
236GO
237
238IF @@TRANCOUNT=0 BEGIN TRANSACTION
239GO
240SET ANSI_NULLS ON
241GO
242SET QUOTED_IDENTIFIER ON
243GO
244
245CREATE TABLE [dbo].[tmp_ALT_STREET_LPI] (
246 [ALT_LPI] [int] IDENTITY (1,1) NOT NULL,
247 [LPI_UNIQUE] [int] NOT NULL,
248 [UPRN] [float] NULL,
249 [USRN] [int] NULL
250) ON [PRIMARY]
251
252IF @@ERROR<>0
253Begin
254 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
255 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ALT_STREET_LPI')
256END
257GO
258SET IDENTITY_INSERT [dbo].[tmp_ALT_STREET_LPI] ON
259IF EXISTS (SELECT * FROM [dbo].[ALT_STREET_LPI])
260 EXEC
261 ('
262 INSERT INTO [dbo].[tmp_ALT_STREET_LPI] ([ALT_LPI],[LPI_UNIQUE],[UPRN],[USRN])
263 SELECT [ALT_LPI],[LPI_UNIQUE],[UPRN],[USRN] FROM [dbo].[ALT_STREET_LPI] TABLOCKX
264 ')
265IF @@ERROR<>0
266Begin
267 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
268 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ALT_STREET_LPI')
269END
270GO
271SET IDENTITY_INSERT [dbo].[tmp_ALT_STREET_LPI] OFF
272
273DROP TABLE [dbo].[ALT_STREET_LPI]
274GO
275IF @@ERROR<>0
276Begin
277 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
278 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ALT_STREET_LPI')
279END
280GO
281EXECUTE sp_rename N'[dbo].[tmp_ALT_STREET_LPI]', N'ALT_STREET_LPI', 'OBJECT'
282
283IF @@ERROR<>0
284Begin
285 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
286 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ALT_STREET_LPI')
287END
288GO
289
290IF @@TRANCOUNT=0 BEGIN TRANSACTION
291GO
292SET ANSI_NULLS ON
293GO
294SET QUOTED_IDENTIFIER ON
295GO
296
297CREATE TABLE [dbo].[tmp_BLPU] (
298 [APGRECNO] [bigint] IDENTITY (1,1) NOT NULL,
299 [UPDDATE] [datetime] NULL,
300 [UPRN] [varchar](12) COLLATE Latin1_General_CI_AS NULL,
301 [DISPLAYFLG] [numeric](1,0) NULL,
302 [LOG_STATUS] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
303 [PROP_TYPE] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
304 [PROV_CODE] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
305 [CAN_KEY] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
306 [XREFKEY] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
307 [PAON] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
308 [SAON] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
309 [STREETNAME] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
310 [LOCALITY] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
311 [STREETTOWN] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
312 [POSTTOWN] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
313 [COUNTY] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
314 [POSTCODE] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
315 [WARDCODE] [varchar](6) COLLATE Latin1_General_CI_AS NULL,
316 [PARISHCODE] [varchar](7) COLLATE Latin1_General_CI_AS NULL,
317 [WARDNAME] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
318 [PARISHNAME] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
319 [BLPUCLASS] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
320 [POSTALADR] [text] COLLATE Latin1_General_CI_AS NULL,
321 [GEOMETRY] [geometry] NULL
322) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
323
324IF @@ERROR<>0
325Begin
326 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
327 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU')
328END
329GO
330SET IDENTITY_INSERT [dbo].[tmp_BLPU] ON
331IF EXISTS (SELECT * FROM [dbo].[BLPU])
332 EXEC
333 ('
334 INSERT INTO [dbo].[tmp_BLPU] ([APGRECNO],[UPDDATE],[UPRN],[DISPLAYFLG],[LOG_STATUS],[PROP_TYPE],[PROV_CODE],[CAN_KEY],[XREFKEY],[PAON],[SAON],[STREETNAME],[LOCALITY],[STREETTOWN],[POSTTOWN],[COUNTY],[POSTCODE],[WARDCODE],[PARISHCODE],[WARDNAME],[PARISHNAME],[BLPUCLASS],[POSTALADR],[GEOMETRY])
335 SELECT [APGRECNO],[UPDDATE],[UPRN],[DISPLAYFLG],[LOG_STATUS],[PROP_TYPE],[PROV_CODE],[CAN_KEY],[XREFKEY],[PAON],[SAON],[STREETNAME],[LOCALITY],[STREETTOWN],[POSTTOWN],[COUNTY],[POSTCODE],[WARDCODE],[PARISHCODE],[WARDNAME],[PARISHNAME],[BLPUCLASS],[POSTALADR],[GEOMETRY] FROM [dbo].[BLPU] TABLOCKX
336 ')
337IF @@ERROR<>0
338Begin
339 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
340 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU')
341END
342GO
343SET IDENTITY_INSERT [dbo].[tmp_BLPU] OFF
344
345DROP TABLE [dbo].[BLPU]
346GO
347IF @@ERROR<>0
348Begin
349 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
350 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU')
351END
352GO
353EXECUTE sp_rename N'[dbo].[tmp_BLPU]', N'BLPU', 'OBJECT'
354
355IF @@ERROR<>0
356Begin
357 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
358 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU')
359END
360GO
361
362IF @@TRANCOUNT=0 BEGIN TRANSACTION
363GO
364SET ANSI_NULLS ON
365GO
366SET QUOTED_IDENTIFIER ON
367GO
368
369CREATE TABLE [dbo].[tmp_BLPU_CANDIDATE] (
370 [BLPU_CANDIDATE_ID] [int] IDENTITY (1,1) NOT NULL,
371 [UPRN] [float] NULL,
372 [CUSTODIAN_CODE] [int] NOT NULL,
373 [XREF] [decimal](10,2) NULL,
374 [YREF] [decimal](10,2) NULL,
375 [LOGICAL_STATUS] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
376 [REP_POINT_CODE] [int] NULL,
377 [START_DATE] [datetime] NULL,
378 [END_DATE] [datetime] NULL,
379 [ENTRY_DATE] [datetime] NULL,
380 [LAST_UPDATE_DATE] [datetime] NULL,
381 [BLPUSTATE] [int] NULL,
382 [BLPU_STATE_DATE] [datetime] NULL,
383 [BLPU_CLASS] [varchar](4) COLLATE Latin1_General_CI_AS NULL,
384 [PARENT_UPRN] [float] NULL,
385 [ORGANISATION] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
386 [WARDCODE] [varchar](6) COLLATE Latin1_General_CI_AS NULL,
387 [PARISHCODE] [varchar](7) COLLATE Latin1_General_CI_AS NULL,
388 [CUSTODIANONE] [int] NULL,
389 [CUSTODIANTWO] [int] NULL,
390 [CAN_KEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
391 [REQUEST_SOURCE] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
392 [CANDIDATE_FLAG] [varchar](3) COLLATE Latin1_General_CI_AS NULL,
393 [NOTES] [varchar](4000) COLLATE Latin1_General_CI_AS NULL,
394 [NEWUNIQUEID] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
395 [REQUEST_SOURCE_ID] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
396 [USER_NAME] [varchar](50) COLLATE Latin1_General_CI_AS NULL
397) ON [PRIMARY]
398
399IF @@ERROR<>0
400Begin
401 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
402 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CANDIDATE')
403END
404GO
405SET IDENTITY_INSERT [dbo].[tmp_BLPU_CANDIDATE] ON
406IF EXISTS (SELECT * FROM [dbo].[BLPU_CANDIDATE])
407 EXEC
408 ('
409 INSERT INTO [dbo].[tmp_BLPU_CANDIDATE] ([BLPU_CANDIDATE_ID],[UPRN],[CUSTODIAN_CODE],[XREF],[YREF],[LOGICAL_STATUS],[REP_POINT_CODE],[START_DATE],[END_DATE],[ENTRY_DATE],[LAST_UPDATE_DATE],[BLPUSTATE],[BLPU_STATE_DATE],[BLPU_CLASS],[PARENT_UPRN],[ORGANISATION],[WARDCODE],[PARISHCODE],[CUSTODIANONE],[CUSTODIANTWO],[CAN_KEY],[REQUEST_SOURCE],[CANDIDATE_FLAG],[NOTES],[NEWUNIQUEID],[REQUEST_SOURCE_ID],[USER_NAME])
410 SELECT [BLPU_CANDIDATE_ID],[UPRN],[CUSTODIAN_CODE],[XREF],[YREF],[LOGICAL_STATUS],[REP_POINT_CODE],[START_DATE],[END_DATE],[ENTRY_DATE],[LAST_UPDATE_DATE],[BLPUSTATE],[BLPU_STATE_DATE],[BLPU_CLASS],[PARENT_UPRN],[ORGANISATION],[WARDCODE],[PARISHCODE],[CUSTODIANONE],[CUSTODIANTWO],[CAN_KEY],[REQUEST_SOURCE],[CANDIDATE_FLAG],[NOTES],[NEWUNIQUEID],[REQUEST_SOURCE_ID],[USER_NAME] FROM [dbo].[BLPU_CANDIDATE] TABLOCKX
411 ')
412IF @@ERROR<>0
413Begin
414 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
415 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CANDIDATE')
416END
417GO
418SET IDENTITY_INSERT [dbo].[tmp_BLPU_CANDIDATE] OFF
419
420DROP TABLE [dbo].[BLPU_CANDIDATE]
421GO
422IF @@ERROR<>0
423Begin
424 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
425 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CANDIDATE')
426END
427GO
428EXECUTE sp_rename N'[dbo].[tmp_BLPU_CANDIDATE]', N'BLPU_CANDIDATE', 'OBJECT'
429
430IF @@ERROR<>0
431Begin
432 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
433 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CANDIDATE')
434END
435GO
436
437IF @@TRANCOUNT=0 BEGIN TRANSACTION
438GO
439SET ANSI_NULLS ON
440GO
441SET QUOTED_IDENTIFIER ON
442GO
443
444CREATE TABLE [dbo].[tmp_BLPU_CLASS] (
445 [BLPU_ID] [int] IDENTITY (1,1) NOT NULL,
446 [Primary_class] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL,
447 [Secondary_Class] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL,
448 [Tertiary_Class] [nvarchar](2) COLLATE Latin1_General_CI_AS NULL,
449 [Description] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
450 [BLPU_Type] [nvarchar](3) COLLATE Latin1_General_CI_AS NULL,
451 [BLPU_Class] [nvarchar](4) COLLATE Latin1_General_CI_AS NULL
452) ON [PRIMARY]
453
454IF @@ERROR<>0
455Begin
456 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
457 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CLASS')
458END
459GO
460SET IDENTITY_INSERT [dbo].[tmp_BLPU_CLASS] ON
461IF EXISTS (SELECT * FROM [dbo].[BLPU_CLASS])
462 EXEC
463 ('
464 INSERT INTO [dbo].[tmp_BLPU_CLASS] ([BLPU_ID],[Primary_class],[Secondary_Class],[Tertiary_Class],[Description],[BLPU_Type],[BLPU_Class])
465 SELECT [BLPU_ID],[Primary_class],[Secondary_Class],[Tertiary_Class],[Description],[BLPU_Type],[BLPU_Class] FROM [dbo].[BLPU_CLASS] TABLOCKX
466 ')
467IF @@ERROR<>0
468Begin
469 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
470 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CLASS')
471END
472GO
473SET IDENTITY_INSERT [dbo].[tmp_BLPU_CLASS] OFF
474
475DROP TABLE [dbo].[BLPU_CLASS]
476GO
477IF @@ERROR<>0
478Begin
479 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
480 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CLASS')
481END
482GO
483EXECUTE sp_rename N'[dbo].[tmp_BLPU_CLASS]', N'BLPU_CLASS', 'OBJECT'
484
485IF @@ERROR<>0
486Begin
487 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
488 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CLASS')
489END
490GO
491
492IF @@TRANCOUNT=0 BEGIN TRANSACTION
493GO
494SET ANSI_NULLS ON
495GO
496SET QUOTED_IDENTIFIER ON
497GO
498
499CREATE TABLE [dbo].[tmp_BLPU_CLASSIFICATION] (
500 [UNIQUE_BLPUCLASS] [int] IDENTITY (1,1) NOT NULL,
501 [UPRN] [float] NOT NULL,
502 [CLASS_KEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
503 [CLASS_SCHEME] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
504 [BLPU_CLASS] [varchar](4) COLLATE Latin1_General_CI_AS NOT NULL,
505 [START_DATE] [datetime] NULL,
506 [END_DATE] [datetime] NULL,
507 [ENTRY_DATE] [datetime] NULL,
508 [LAST_UPDATE_DATE] [datetime] NULL,
509 [LOCALONLY_PRIORITY] [varchar](3) COLLATE Latin1_General_CI_AS NULL,
510 [LOCALONLY_BLPUCLASSKEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
511 [CAN_KEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
512 [LOCALONLY_UNIQUEID] [float] NOT NULL
513) ON [PRIMARY]
514
515IF @@ERROR<>0
516Begin
517 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
518 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CLASSIFICATION')
519END
520GO
521SET IDENTITY_INSERT [dbo].[tmp_BLPU_CLASSIFICATION] ON
522IF EXISTS (SELECT * FROM [dbo].[BLPU_CLASSIFICATION])
523 EXEC
524 ('
525 INSERT INTO [dbo].[tmp_BLPU_CLASSIFICATION] ([UNIQUE_BLPUCLASS],[UPRN],[CLASS_KEY],[CLASS_SCHEME],[BLPU_CLASS],[START_DATE],[END_DATE],[ENTRY_DATE],[LAST_UPDATE_DATE],[LOCALONLY_PRIORITY],[LOCALONLY_BLPUCLASSKEY],[CAN_KEY],[LOCALONLY_UNIQUEID])
526 SELECT [UNIQUE_BLPUCLASS],[UPRN],[CLASS_KEY],[CLASS_SCHEME],[BLPU_CLASS],[START_DATE],[END_DATE],[ENTRY_DATE],[LAST_UPDATE_DATE],[LOCALONLY_PRIORITY],[LOCALONLY_BLPUCLASSKEY],[CAN_KEY],[LOCALONLY_UNIQUEID] FROM [dbo].[BLPU_CLASSIFICATION] TABLOCKX
527 ')
528IF @@ERROR<>0
529Begin
530 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
531 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CLASSIFICATION')
532END
533GO
534SET IDENTITY_INSERT [dbo].[tmp_BLPU_CLASSIFICATION] OFF
535
536DROP TABLE [dbo].[BLPU_CLASSIFICATION]
537GO
538IF @@ERROR<>0
539Begin
540 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
541 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CLASSIFICATION')
542END
543GO
544EXECUTE sp_rename N'[dbo].[tmp_BLPU_CLASSIFICATION]', N'BLPU_CLASSIFICATION', 'OBJECT'
545
546IF @@ERROR<>0
547Begin
548 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
549 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.BLPU_CLASSIFICATION')
550END
551GO
552
553IF @@TRANCOUNT=0 BEGIN TRANSACTION
554GO
555SET ANSI_NULLS ON
556GO
557SET QUOTED_IDENTIFIER ON
558GO
559
560CREATE TABLE [dbo].[tmp_Candidate_TFI] (
561 [TFI_Unique] [int] IDENTITY (1,1) NOT NULL,
562 [KEy_value] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
563 [entire_record] [varchar](2000) COLLATE Latin1_General_CI_AS NULL,
564 [entry_date] [datetime] NULL,
565 [USERID] [varchar](50) COLLATE Latin1_General_CI_AS NULL
566) ON [PRIMARY]
567
568IF @@ERROR<>0
569Begin
570 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
571 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.Candidate_TFI')
572END
573GO
574SET IDENTITY_INSERT [dbo].[tmp_Candidate_TFI] ON
575IF EXISTS (SELECT * FROM [dbo].[Candidate_TFI])
576 EXEC
577 ('
578 INSERT INTO [dbo].[tmp_Candidate_TFI] ([TFI_Unique],[KEy_value],[entire_record],[entry_date],[USERID])
579 SELECT [TFI_Unique],[KEy_value],[entire_record],[entry_date],[USERID] FROM [dbo].[Candidate_TFI] TABLOCKX
580 ')
581IF @@ERROR<>0
582Begin
583 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
584 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.Candidate_TFI')
585END
586GO
587SET IDENTITY_INSERT [dbo].[tmp_Candidate_TFI] OFF
588
589DROP TABLE [dbo].[Candidate_TFI]
590GO
591IF @@ERROR<>0
592Begin
593 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
594 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.Candidate_TFI')
595END
596GO
597EXECUTE sp_rename N'[dbo].[tmp_Candidate_TFI]', N'Candidate_TFI', 'OBJECT'
598
599IF @@ERROR<>0
600Begin
601 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
602 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.Candidate_TFI')
603END
604GO
605
606IF @@TRANCOUNT=0 BEGIN TRANSACTION
607GO
608SET ANSI_NULLS ON
609GO
610SET QUOTED_IDENTIFIER ON
611GO
612
613CREATE TABLE [dbo].[tmp_Candidate_TFI_ARCHIVE] (
614 [TFI_ARCHIVE_Unique] [int] IDENTITY (1,1) NOT NULL,
615 [KEy_value] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
616 [entire_record] [varchar](2000) COLLATE Latin1_General_CI_AS NULL,
617 [entry_date] [datetime] NULL
618) ON [PRIMARY]
619
620IF @@ERROR<>0
621Begin
622 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
623 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.Candidate_TFI_ARCHIVE')
624END
625GO
626SET IDENTITY_INSERT [dbo].[tmp_Candidate_TFI_ARCHIVE] ON
627IF EXISTS (SELECT * FROM [dbo].[Candidate_TFI_ARCHIVE])
628 EXEC
629 ('
630 INSERT INTO [dbo].[tmp_Candidate_TFI_ARCHIVE] ([TFI_ARCHIVE_Unique],[KEy_value],[entire_record],[entry_date])
631 SELECT [TFI_ARCHIVE_Unique],[KEy_value],[entire_record],[entry_date] FROM [dbo].[Candidate_TFI_ARCHIVE] TABLOCKX
632 ')
633IF @@ERROR<>0
634Begin
635 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
636 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.Candidate_TFI_ARCHIVE')
637END
638GO
639SET IDENTITY_INSERT [dbo].[tmp_Candidate_TFI_ARCHIVE] OFF
640
641DROP TABLE [dbo].[Candidate_TFI_ARCHIVE]
642GO
643IF @@ERROR<>0
644Begin
645 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
646 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.Candidate_TFI_ARCHIVE')
647END
648GO
649EXECUTE sp_rename N'[dbo].[tmp_Candidate_TFI_ARCHIVE]', N'Candidate_TFI_ARCHIVE', 'OBJECT'
650
651IF @@ERROR<>0
652Begin
653 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
654 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.Candidate_TFI_ARCHIVE')
655END
656GO
657
658IF @@TRANCOUNT=0 BEGIN TRANSACTION
659GO
660SET ANSI_NULLS ON
661GO
662SET QUOTED_IDENTIFIER ON
663GO
664
665CREATE TABLE [dbo].[tmp_ESU] (
666 [APGRECNO] [bigint] IDENTITY (1,1) NOT NULL,
667 [UPDDATE] [datetime] NULL,
668 [ESU_ID] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
669 [DISPLAYFLG] [numeric](1,0) NULL,
670 [STREETNAME] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
671 [TOWNNAME] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
672 [LOCALITY] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
673 [USRN] [varchar](8) COLLATE Latin1_General_CI_AS NULL,
674 [GEOMETRY] [geometry] NULL
675) ON [PRIMARY]
676
677IF @@ERROR<>0
678Begin
679 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
680 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ESU')
681END
682GO
683SET IDENTITY_INSERT [dbo].[tmp_ESU] ON
684IF EXISTS (SELECT * FROM [dbo].[ESU])
685 EXEC
686 ('
687 INSERT INTO [dbo].[tmp_ESU] ([APGRECNO],[UPDDATE],[ESU_ID],[DISPLAYFLG],[STREETNAME],[TOWNNAME],[LOCALITY],[USRN],[GEOMETRY])
688 SELECT [APGRECNO],[UPDDATE],[ESU_ID],[DISPLAYFLG],[STREETNAME],[TOWNNAME],[LOCALITY],[USRN],[GEOMETRY] FROM [dbo].[ESU] TABLOCKX
689 ')
690IF @@ERROR<>0
691Begin
692 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
693 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ESU')
694END
695GO
696SET IDENTITY_INSERT [dbo].[tmp_ESU] OFF
697
698DROP TABLE [dbo].[ESU]
699GO
700IF @@ERROR<>0
701Begin
702 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
703 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ESU')
704END
705GO
706EXECUTE sp_rename N'[dbo].[tmp_ESU]', N'ESU', 'OBJECT'
707
708IF @@ERROR<>0
709Begin
710 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
711 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ESU')
712END
713GO
714
715IF @@TRANCOUNT=0 BEGIN TRANSACTION
716GO
717SET ANSI_NULLS ON
718GO
719SET QUOTED_IDENTIFIER ON
720GO
721
722CREATE TABLE [dbo].[tmp_EXPORT_DATES] (
723 [EXPORT_ID] [int] IDENTITY (1,1) NOT NULL,
724 [EXPORT_DATE] [datetime] NULL,
725 [STATUS_IMPORT_DATE] [datetime] NULL,
726 [HUB_EXPORT_DATE] [datetime] NULL,
727 [EXPORT_TYPE] [int] NOT NULL,
728 [USERID] [varchar](50) COLLATE Latin1_General_CI_AS NULL
729) ON [PRIMARY]
730
731IF @@ERROR<>0
732Begin
733 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
734 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.EXPORT_DATES')
735END
736GO
737SET IDENTITY_INSERT [dbo].[tmp_EXPORT_DATES] ON
738IF EXISTS (SELECT * FROM [dbo].[EXPORT_DATES])
739 EXEC
740 ('
741 INSERT INTO [dbo].[tmp_EXPORT_DATES] ([EXPORT_ID],[EXPORT_DATE],[STATUS_IMPORT_DATE],[HUB_EXPORT_DATE],[EXPORT_TYPE],[USERID])
742 SELECT [EXPORT_ID],[EXPORT_DATE],[STATUS_IMPORT_DATE],[HUB_EXPORT_DATE],[EXPORT_TYPE],[USERID] FROM [dbo].[EXPORT_DATES] TABLOCKX
743 ')
744IF @@ERROR<>0
745Begin
746 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
747 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.EXPORT_DATES')
748END
749GO
750SET IDENTITY_INSERT [dbo].[tmp_EXPORT_DATES] OFF
751
752DROP TABLE [dbo].[EXPORT_DATES]
753GO
754IF @@ERROR<>0
755Begin
756 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
757 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.EXPORT_DATES')
758END
759GO
760EXECUTE sp_rename N'[dbo].[tmp_EXPORT_DATES]', N'EXPORT_DATES', 'OBJECT'
761
762IF @@ERROR<>0
763Begin
764 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
765 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.EXPORT_DATES')
766END
767GO
768
769IF @@TRANCOUNT=0 BEGIN TRANSACTION
770GO
771SET ANSI_NULLS ON
772GO
773SET QUOTED_IDENTIFIER ON
774GO
775
776CREATE TABLE [dbo].[tmp_EXPORTDATES_TFIOLD] (
777 [EXPORT_ID] [int] IDENTITY (1,1) NOT NULL,
778 [EXPORT_DATE] [datetime] NULL,
779 [STATUS_IMPORT_DATE] [datetime] NULL,
780 [HUB_EXPORT_DATE] [datetime] NULL,
781 [EXPORT_TYPE] [int] NULL,
782 [ENTRY_DATE] [datetime] NULL
783) ON [PRIMARY]
784
785IF @@ERROR<>0
786Begin
787 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
788 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.EXPORTDATES_TFIOLD')
789END
790GO
791SET IDENTITY_INSERT [dbo].[tmp_EXPORTDATES_TFIOLD] ON
792IF EXISTS (SELECT * FROM [dbo].[EXPORTDATES_TFIOLD])
793 EXEC
794 ('
795 INSERT INTO [dbo].[tmp_EXPORTDATES_TFIOLD] ([EXPORT_ID],[EXPORT_DATE],[STATUS_IMPORT_DATE],[HUB_EXPORT_DATE],[EXPORT_TYPE],[ENTRY_DATE])
796 SELECT [EXPORT_ID],[EXPORT_DATE],[STATUS_IMPORT_DATE],[HUB_EXPORT_DATE],[EXPORT_TYPE],[ENTRY_DATE] FROM [dbo].[EXPORTDATES_TFIOLD] TABLOCKX
797 ')
798IF @@ERROR<>0
799Begin
800 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
801 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.EXPORTDATES_TFIOLD')
802END
803GO
804SET IDENTITY_INSERT [dbo].[tmp_EXPORTDATES_TFIOLD] OFF
805
806DROP TABLE [dbo].[EXPORTDATES_TFIOLD]
807GO
808IF @@ERROR<>0
809Begin
810 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
811 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.EXPORTDATES_TFIOLD')
812END
813GO
814EXECUTE sp_rename N'[dbo].[tmp_EXPORTDATES_TFIOLD]', N'EXPORTDATES_TFIOLD', 'OBJECT'
815
816IF @@ERROR<>0
817Begin
818 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
819 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.EXPORTDATES_TFIOLD')
820END
821GO
822
823IF @@TRANCOUNT=0 BEGIN TRANSACTION
824GO
825SET ANSI_NULLS ON
826GO
827SET QUOTED_IDENTIFIER ON
828GO
829
830CREATE TABLE [dbo].[tmp_HWW] (
831 [APGRECNO] [bigint] IDENTITY (1,1) NOT NULL,
832 [UPDDATE] [datetime] NULL,
833 [USRN] [varchar](8) COLLATE Latin1_General_CI_AS NULL,
834 [SEQNUM] [numeric](3,0) NULL,
835 [DESIGCODE] [varchar](120) COLLATE Latin1_General_CI_AS NULL,
836 [WHOLE_ROAD] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
837 [ENDDATE] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
838 [LOCTEXT] [varchar](120) COLLATE Latin1_General_CI_AS NULL,
839 [VALMETRIC] [varchar](3) COLLATE Latin1_General_CI_AS NULL,
840 [TROTEXT] [varchar](120) COLLATE Latin1_General_CI_AS NULL,
841 [FEATDESC] [varchar](120) COLLATE Latin1_General_CI_AS NULL,
842 [AUTHORITY] [varchar](4) COLLATE Latin1_General_CI_AS NULL,
843 [GEOMETRY] [geometry] NULL
844) ON [PRIMARY]
845
846IF @@ERROR<>0
847Begin
848 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
849 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.HWW')
850END
851GO
852SET IDENTITY_INSERT [dbo].[tmp_HWW] ON
853IF EXISTS (SELECT * FROM [dbo].[HWW])
854 EXEC
855 ('
856 INSERT INTO [dbo].[tmp_HWW] ([APGRECNO],[UPDDATE],[USRN],[SEQNUM],[DESIGCODE],[WHOLE_ROAD],[ENDDATE],[LOCTEXT],[VALMETRIC],[TROTEXT],[FEATDESC],[AUTHORITY],[GEOMETRY])
857 SELECT [APGRECNO],[UPDDATE],[USRN],[SEQNUM],[DESIGCODE],[WHOLE_ROAD],[ENDDATE],[LOCTEXT],[VALMETRIC],[TROTEXT],[FEATDESC],[AUTHORITY],[GEOMETRY] FROM [dbo].[HWW] TABLOCKX
858 ')
859IF @@ERROR<>0
860Begin
861 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
862 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.HWW')
863END
864GO
865SET IDENTITY_INSERT [dbo].[tmp_HWW] OFF
866
867DROP TABLE [dbo].[HWW]
868GO
869IF @@ERROR<>0
870Begin
871 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
872 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.HWW')
873END
874GO
875EXECUTE sp_rename N'[dbo].[tmp_HWW]', N'HWW', 'OBJECT'
876
877IF @@ERROR<>0
878Begin
879 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
880 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.HWW')
881END
882GO
883
884IF @@TRANCOUNT=0 BEGIN TRANSACTION
885GO
886SET ANSI_NULLS ON
887GO
888SET QUOTED_IDENTIFIER ON
889GO
890
891CREATE TABLE [dbo].[tmp_LAND_AND_PROPERTY_IDENTIFIER] (
892 [LPI_UNIQUE] [int] IDENTITY (1,1) NOT NULL,
893 [UPRN] [float] NOT NULL,
894 [USRN] [int] NOT NULL,
895 [PAO_FROM] [smallint] NULL,
896 [PAO_FROM_SUFF] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
897 [PAO_TO] [smallint] NULL,
898 [PAO_TO_SUFF] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
899 [PAO_NAME] [varchar](90) COLLATE Latin1_General_CI_AS NULL,
900 [SAO_FROM] [smallint] NULL,
901 [SAO_FROM_SUFF] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
902 [SAO_TO] [smallint] NULL,
903 [SAO_TO_SUFF] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
904 [SAO_NAME] [varchar](90) COLLATE Latin1_General_CI_AS NULL,
905 [POSTTOWN] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
906 [POSTCODE] [varchar](8) COLLATE Latin1_General_CI_AS NULL,
907 [OFFICIAL_ADDRESS_MARKER] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
908 [LPI_LEVEL] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
909 [LOGICAL_STATUS] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
910 [START_DATE] [datetime] NULL,
911 [END_DATE] [datetime] NULL,
912 [ENTRY_DATE] [datetime] NULL,
913 [LAST_UPDATE_DATE] [datetime] NULL,
914 [ALT_LPI] [int] NULL,
915 [LPIKEY] [nvarchar](14) COLLATE Latin1_General_CI_AS NULL,
916 [POSTALLYADDRESSABLE] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL,
917 [CUSTODIANONE] [int] NULL,
918 [CUSTODIANTWO] [int] NULL,
919 [CAN_KEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
920 [LANGUAGE_CODE] [nvarchar](3) COLLATE Latin1_General_CI_AS NULL,
921 [HIDEPAON] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL,
922 [SUB_LOCALITY] [varchar](35) COLLATE Latin1_General_CI_AS NULL,
923 [ALIAS_ID] [int] NULL,
924 [LOCALONLY_PRIORITY] [varchar](3) COLLATE Latin1_General_CI_AS NULL,
925 [POLICEBEATCODE] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
926 [POLICEFORCEAREA] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
927 [LOCALONLY_UNIQUE_ID] [float] NOT NULL,
928 [LPI_NOTES] [nvarchar](2000) COLLATE Latin1_General_CI_AS NULL,
929 [LOCALONLY_LPIKEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
930 [ISOSGSTREETRECORD] [varchar](1) COLLATE Latin1_General_CI_AS NULL
931) ON [PRIMARY]
932
933IF @@ERROR<>0
934Begin
935 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
936 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LAND_AND_PROPERTY_IDENTIFIER')
937END
938GO
939SET IDENTITY_INSERT [dbo].[tmp_LAND_AND_PROPERTY_IDENTIFIER] ON
940IF EXISTS (SELECT * FROM [dbo].[LAND_AND_PROPERTY_IDENTIFIER])
941 EXEC
942 ('
943 INSERT INTO [dbo].[tmp_LAND_AND_PROPERTY_IDENTIFIER] ([LPI_UNIQUE],[UPRN],[USRN],[PAO_FROM],[PAO_FROM_SUFF],[PAO_TO],[PAO_TO_SUFF],[PAO_NAME],[SAO_FROM],[SAO_FROM_SUFF],[SAO_TO],[SAO_TO_SUFF],[SAO_NAME],[POSTTOWN],[POSTCODE],[OFFICIAL_ADDRESS_MARKER],[LPI_LEVEL],[LOGICAL_STATUS],[START_DATE],[END_DATE],[ENTRY_DATE],[LAST_UPDATE_DATE],[ALT_LPI],[LPIKEY],[POSTALLYADDRESSABLE],[CUSTODIANONE],[CUSTODIANTWO],[CAN_KEY],[LANGUAGE_CODE],[HIDEPAON],[SUB_LOCALITY],[ALIAS_ID],[LOCALONLY_PRIORITY],[POLICEBEATCODE],[POLICEFORCEAREA],[LOCALONLY_UNIQUE_ID],[LPI_NOTES],[LOCALONLY_LPIKEY],[ISOSGSTREETRECORD])
944 SELECT [LPI_UNIQUE],[UPRN],[USRN],[PAO_FROM],[PAO_FROM_SUFF],[PAO_TO],[PAO_TO_SUFF],[PAO_NAME],[SAO_FROM],[SAO_FROM_SUFF],[SAO_TO],[SAO_TO_SUFF],[SAO_NAME],[POSTTOWN],[POSTCODE],[OFFICIAL_ADDRESS_MARKER],[LPI_LEVEL],[LOGICAL_STATUS],[START_DATE],[END_DATE],[ENTRY_DATE],[LAST_UPDATE_DATE],[ALT_LPI],[LPIKEY],[POSTALLYADDRESSABLE],[CUSTODIANONE],[CUSTODIANTWO],[CAN_KEY],[LANGUAGE_CODE],[HIDEPAON],[SUB_LOCALITY],[ALIAS_ID],[LOCALONLY_PRIORITY],[POLICEBEATCODE],[POLICEFORCEAREA],[LOCALONLY_UNIQUE_ID],[LPI_NOTES],[LOCALONLY_LPIKEY],[ISOSGSTREETRECORD] FROM [dbo].[LAND_AND_PROPERTY_IDENTIFIER] TABLOCKX
945 ')
946IF @@ERROR<>0
947Begin
948 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
949 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LAND_AND_PROPERTY_IDENTIFIER')
950END
951GO
952SET IDENTITY_INSERT [dbo].[tmp_LAND_AND_PROPERTY_IDENTIFIER] OFF
953
954DROP TABLE [dbo].[LAND_AND_PROPERTY_IDENTIFIER]
955GO
956IF @@ERROR<>0
957Begin
958 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
959 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LAND_AND_PROPERTY_IDENTIFIER')
960END
961GO
962EXECUTE sp_rename N'[dbo].[tmp_LAND_AND_PROPERTY_IDENTIFIER]', N'LAND_AND_PROPERTY_IDENTIFIER', 'OBJECT'
963
964IF @@ERROR<>0
965Begin
966 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
967 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LAND_AND_PROPERTY_IDENTIFIER')
968END
969GO
970
971IF @@TRANCOUNT=0 BEGIN TRANSACTION
972GO
973SET ANSI_NULLS ON
974GO
975SET QUOTED_IDENTIFIER ON
976GO
977
978CREATE TABLE [dbo].[tmp_LOCALITY] (
979 [LOC_REF] [int] IDENTITY (1,1) NOT NULL,
980 [NAME] [varchar](35) COLLATE Latin1_General_CI_AS NULL,
981 [TYPE] [varchar](1) COLLATE Latin1_General_CI_AS NULL
982) ON [PRIMARY]
983
984IF @@ERROR<>0
985Begin
986 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
987 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LOCALITY')
988END
989GO
990SET IDENTITY_INSERT [dbo].[tmp_LOCALITY] ON
991IF EXISTS (SELECT * FROM [dbo].[LOCALITY])
992 EXEC
993 ('
994 INSERT INTO [dbo].[tmp_LOCALITY] ([LOC_REF],[NAME],[TYPE])
995 SELECT [LOC_REF],[NAME],[TYPE] FROM [dbo].[LOCALITY] TABLOCKX
996 ')
997IF @@ERROR<>0
998Begin
999 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1000 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LOCALITY')
1001END
1002GO
1003SET IDENTITY_INSERT [dbo].[tmp_LOCALITY] OFF
1004
1005DROP TABLE [dbo].[LOCALITY]
1006GO
1007IF @@ERROR<>0
1008Begin
1009 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1010 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LOCALITY')
1011END
1012GO
1013EXECUTE sp_rename N'[dbo].[tmp_LOCALITY]', N'LOCALITY', 'OBJECT'
1014
1015IF @@ERROR<>0
1016Begin
1017 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1018 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LOCALITY')
1019END
1020GO
1021
1022IF @@TRANCOUNT=0 BEGIN TRANSACTION
1023GO
1024SET ANSI_NULLS ON
1025GO
1026SET QUOTED_IDENTIFIER ON
1027GO
1028
1029CREATE TABLE [dbo].[tmp_LPI_CANDIDATE] (
1030 [LPI_CANDIDATE_ID] [int] IDENTITY (1,1) NOT NULL,
1031 [UPRN] [float] NULL,
1032 [USRN] [int] NOT NULL,
1033 [PAO_FROM] [smallint] NULL,
1034 [PAO_FROM_SUFF] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
1035 [PAO_TO] [smallint] NULL,
1036 [PAO_TO_SUFF] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
1037 [PAO_NAME] [varchar](90) COLLATE Latin1_General_CI_AS NULL,
1038 [SAO_FROM] [smallint] NULL,
1039 [SAO_FROM_SUFF] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
1040 [SAO_TO] [smallint] NULL,
1041 [SAO_TO_SUFF] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
1042 [SAO_NAME] [varchar](90) COLLATE Latin1_General_CI_AS NULL,
1043 [POSTTOWN] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
1044 [POSTCODE] [varchar](8) COLLATE Latin1_General_CI_AS NULL,
1045 [LPI_LEVEL] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
1046 [LOGICAL_STATUS] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
1047 [START_DATE] [datetime] NULL,
1048 [END_DATE] [datetime] NULL,
1049 [ENTRY_DATE] [datetime] NULL,
1050 [LAST_UPDATE_DATE] [datetime] NULL,
1051 [LPIKEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
1052 [POSTALLYADDRESSABLE] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
1053 [CUSTODIANONE] [int] NULL,
1054 [CUSTODIANTWO] [int] NULL,
1055 [CAN_KEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
1056 [LANGUAGE_CODE] [varchar](3) COLLATE Latin1_General_CI_AS NULL,
1057 [REQUEST_SOURCE] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
1058 [CANDIDATE_FLAG] [varchar](3) COLLATE Latin1_General_CI_AS NULL,
1059 [OFFICIAL_FLAG] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
1060 [SUB_LOCALITY] [varchar](35) COLLATE Latin1_General_CI_AS NULL,
1061 [NOTES] [varchar](4000) COLLATE Latin1_General_CI_AS NULL,
1062 [COMMENTS] [varchar](4000) COLLATE Latin1_General_CI_AS NULL,
1063 [NEWUNIQUEID] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
1064 [USER_NAME] [varchar](50) COLLATE Latin1_General_CI_AS NULL
1065) ON [PRIMARY]
1066
1067IF @@ERROR<>0
1068Begin
1069 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1070 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LPI_CANDIDATE')
1071END
1072GO
1073SET IDENTITY_INSERT [dbo].[tmp_LPI_CANDIDATE] ON
1074IF EXISTS (SELECT * FROM [dbo].[LPI_CANDIDATE])
1075 EXEC
1076 ('
1077 INSERT INTO [dbo].[tmp_LPI_CANDIDATE] ([LPI_CANDIDATE_ID],[UPRN],[USRN],[PAO_FROM],[PAO_FROM_SUFF],[PAO_TO],[PAO_TO_SUFF],[PAO_NAME],[SAO_FROM],[SAO_FROM_SUFF],[SAO_TO],[SAO_TO_SUFF],[SAO_NAME],[POSTTOWN],[POSTCODE],[LPI_LEVEL],[LOGICAL_STATUS],[START_DATE],[END_DATE],[ENTRY_DATE],[LAST_UPDATE_DATE],[LPIKEY],[POSTALLYADDRESSABLE],[CUSTODIANONE],[CUSTODIANTWO],[CAN_KEY],[LANGUAGE_CODE],[REQUEST_SOURCE],[CANDIDATE_FLAG],[OFFICIAL_FLAG],[SUB_LOCALITY],[NOTES],[COMMENTS],[NEWUNIQUEID],[USER_NAME])
1078 SELECT [LPI_CANDIDATE_ID],[UPRN],[USRN],[PAO_FROM],[PAO_FROM_SUFF],[PAO_TO],[PAO_TO_SUFF],[PAO_NAME],[SAO_FROM],[SAO_FROM_SUFF],[SAO_TO],[SAO_TO_SUFF],[SAO_NAME],[POSTTOWN],[POSTCODE],[LPI_LEVEL],[LOGICAL_STATUS],[START_DATE],[END_DATE],[ENTRY_DATE],[LAST_UPDATE_DATE],[LPIKEY],[POSTALLYADDRESSABLE],[CUSTODIANONE],[CUSTODIANTWO],[CAN_KEY],[LANGUAGE_CODE],[REQUEST_SOURCE],[CANDIDATE_FLAG],[OFFICIAL_FLAG],[SUB_LOCALITY],[NOTES],[COMMENTS],[NEWUNIQUEID],[USER_NAME] FROM [dbo].[LPI_CANDIDATE] TABLOCKX
1079 ')
1080IF @@ERROR<>0
1081Begin
1082 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1083 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LPI_CANDIDATE')
1084END
1085GO
1086SET IDENTITY_INSERT [dbo].[tmp_LPI_CANDIDATE] OFF
1087
1088DROP TABLE [dbo].[LPI_CANDIDATE]
1089GO
1090IF @@ERROR<>0
1091Begin
1092 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1093 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LPI_CANDIDATE')
1094END
1095GO
1096EXECUTE sp_rename N'[dbo].[tmp_LPI_CANDIDATE]', N'LPI_CANDIDATE', 'OBJECT'
1097
1098IF @@ERROR<>0
1099Begin
1100 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1101 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.LPI_CANDIDATE')
1102END
1103GO
1104
1105IF @@TRANCOUNT=0 BEGIN TRANSACTION
1106GO
1107SET ANSI_NULLS ON
1108GO
1109SET QUOTED_IDENTIFIER ON
1110GO
1111
1112CREATE TABLE [dbo].[tmp_NODE] (
1113 [APGRECNO] [bigint] IDENTITY (1,1) NOT NULL,
1114 [UPDDATE] [datetime] NULL,
1115 [NODE_ID] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
1116 [DISPLAYFLG] [numeric](1,0) NULL,
1117 [GEOMETRY] [geometry] NULL
1118) ON [PRIMARY]
1119
1120IF @@ERROR<>0
1121Begin
1122 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1123 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.NODE')
1124END
1125GO
1126SET IDENTITY_INSERT [dbo].[tmp_NODE] ON
1127IF EXISTS (SELECT * FROM [dbo].[NODE])
1128 EXEC
1129 ('
1130 INSERT INTO [dbo].[tmp_NODE] ([APGRECNO],[UPDDATE],[NODE_ID],[DISPLAYFLG],[GEOMETRY])
1131 SELECT [APGRECNO],[UPDDATE],[NODE_ID],[DISPLAYFLG],[GEOMETRY] FROM [dbo].[NODE] TABLOCKX
1132 ')
1133IF @@ERROR<>0
1134Begin
1135 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1136 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.NODE')
1137END
1138GO
1139SET IDENTITY_INSERT [dbo].[tmp_NODE] OFF
1140
1141DROP TABLE [dbo].[NODE]
1142GO
1143IF @@ERROR<>0
1144Begin
1145 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1146 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.NODE')
1147END
1148GO
1149EXECUTE sp_rename N'[dbo].[tmp_NODE]', N'NODE', 'OBJECT'
1150
1151IF @@ERROR<>0
1152Begin
1153 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1154 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.NODE')
1155END
1156GO
1157
1158IF @@TRANCOUNT=0 BEGIN TRANSACTION
1159GO
1160SET ANSI_NULLS ON
1161GO
1162SET QUOTED_IDENTIFIER ON
1163GO
1164
1165CREATE TABLE [dbo].[tmp_ORGANISATION] (
1166 [UNIQUE_ORG] [int] IDENTITY (1,1) NOT NULL,
1167 [UPRN] [float] NOT NULL,
1168 [ORG_KEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
1169 [ORGANISATION] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
1170 [LEGAL_NAME] [varchar](60) COLLATE Latin1_General_CI_AS NULL,
1171 [START_DATE] [datetime] NULL,
1172 [END_DATE] [datetime] NULL,
1173 [ENTRY_DATE] [datetime] NULL,
1174 [LAST_UPDATE_DATE] [datetime] NULL,
1175 [LOCALONLY_PRIORITY] [varchar](3) COLLATE Latin1_General_CI_AS NULL,
1176 [LOCALONLY_ORGKEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
1177 [CAN_KEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
1178 [LOCALONLY_UNIQUEID] [float] NOT NULL
1179) ON [PRIMARY]
1180
1181IF @@ERROR<>0
1182Begin
1183 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1184 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ORGANISATION')
1185END
1186GO
1187SET IDENTITY_INSERT [dbo].[tmp_ORGANISATION] ON
1188IF EXISTS (SELECT * FROM [dbo].[ORGANISATION])
1189 EXEC
1190 ('
1191 INSERT INTO [dbo].[tmp_ORGANISATION] ([UNIQUE_ORG],[UPRN],[ORG_KEY],[ORGANISATION],[LEGAL_NAME],[START_DATE],[END_DATE],[ENTRY_DATE],[LAST_UPDATE_DATE],[LOCALONLY_PRIORITY],[LOCALONLY_ORGKEY],[CAN_KEY],[LOCALONLY_UNIQUEID])
1192 SELECT [UNIQUE_ORG],[UPRN],[ORG_KEY],[ORGANISATION],[LEGAL_NAME],[START_DATE],[END_DATE],[ENTRY_DATE],[LAST_UPDATE_DATE],[LOCALONLY_PRIORITY],[LOCALONLY_ORGKEY],[CAN_KEY],[LOCALONLY_UNIQUEID] FROM [dbo].[ORGANISATION] TABLOCKX
1193 ')
1194IF @@ERROR<>0
1195Begin
1196 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1197 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ORGANISATION')
1198END
1199GO
1200SET IDENTITY_INSERT [dbo].[tmp_ORGANISATION] OFF
1201
1202DROP TABLE [dbo].[ORGANISATION]
1203GO
1204IF @@ERROR<>0
1205Begin
1206 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1207 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ORGANISATION')
1208END
1209GO
1210EXECUTE sp_rename N'[dbo].[tmp_ORGANISATION]', N'ORGANISATION', 'OBJECT'
1211
1212IF @@ERROR<>0
1213Begin
1214 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1215 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.ORGANISATION')
1216END
1217GO
1218
1219IF @@TRANCOUNT=0 BEGIN TRANSACTION
1220GO
1221SET ANSI_NULLS ON
1222GO
1223SET QUOTED_IDENTIFIER ON
1224GO
1225
1226CREATE TABLE [dbo].[tmp_REINSTDS] (
1227 [APGRECNO] [bigint] IDENTITY (1,1) NOT NULL,
1228 [UPDDATE] [datetime] NULL,
1229 [USRN] [varchar](8) COLLATE Latin1_General_CI_AS NULL,
1230 [SRCTYPE] [varchar](250) COLLATE Latin1_General_CI_AS NULL,
1231 [WHOLE_ROAD] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
1232 [LOCTEXT] [varchar](250) COLLATE Latin1_General_CI_AS NULL,
1233 [SEQNUM] [numeric](3,0) NULL,
1234 [SRCCODE] [numeric](2,0) NULL,
1235 [GEOMETRY] [geometry] NULL
1236) ON [PRIMARY]
1237
1238IF @@ERROR<>0
1239Begin
1240 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1241 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.REINSTDS')
1242END
1243GO
1244SET IDENTITY_INSERT [dbo].[tmp_REINSTDS] ON
1245IF EXISTS (SELECT * FROM [dbo].[REINSTDS])
1246 EXEC
1247 ('
1248 INSERT INTO [dbo].[tmp_REINSTDS] ([APGRECNO],[UPDDATE],[USRN],[SRCTYPE],[WHOLE_ROAD],[LOCTEXT],[SEQNUM],[SRCCODE],[GEOMETRY])
1249 SELECT [APGRECNO],[UPDDATE],[USRN],[SRCTYPE],[WHOLE_ROAD],[LOCTEXT],[SEQNUM],[SRCCODE],[GEOMETRY] FROM [dbo].[REINSTDS] TABLOCKX
1250 ')
1251IF @@ERROR<>0
1252Begin
1253 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1254 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.REINSTDS')
1255END
1256GO
1257SET IDENTITY_INSERT [dbo].[tmp_REINSTDS] OFF
1258
1259DROP TABLE [dbo].[REINSTDS]
1260GO
1261IF @@ERROR<>0
1262Begin
1263 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1264 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.REINSTDS')
1265END
1266GO
1267EXECUTE sp_rename N'[dbo].[tmp_REINSTDS]', N'REINSTDS', 'OBJECT'
1268
1269IF @@ERROR<>0
1270Begin
1271 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1272 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.REINSTDS')
1273END
1274GO
1275
1276IF @@TRANCOUNT=0 BEGIN TRANSACTION
1277GO
1278SET ANSI_NULLS ON
1279GO
1280SET QUOTED_IDENTIFIER ON
1281GO
1282
1283CREATE TABLE [dbo].[tmp_SPCLDSG] (
1284 [APGRECNO] [bigint] IDENTITY (1,1) NOT NULL,
1285 [UPDDATE] [datetime] NULL,
1286 [USRN] [varchar](8) COLLATE Latin1_General_CI_AS NULL,
1287 [SPLDSGTEXT] [varchar](250) COLLATE Latin1_General_CI_AS NULL,
1288 [WHOLE_ROAD] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
1289 [SPLDSGPRD] [varchar](120) COLLATE Latin1_General_CI_AS NULL,
1290 [LOCTEXT] [varchar](250) COLLATE Latin1_General_CI_AS NULL,
1291 [SEQNUM] [numeric](3,0) NULL,
1292 [DESIGCODE] [numeric](2,0) NULL,
1293 [STARTDATE] [date] NULL,
1294 [ENDDATE] [date] NULL,
1295 [START_TIME] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
1296 [END_TIME] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
1297 [GEOMETRY] [geometry] NULL
1298) ON [PRIMARY]
1299
1300IF @@ERROR<>0
1301Begin
1302 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1303 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.SPCLDSG')
1304END
1305GO
1306SET IDENTITY_INSERT [dbo].[tmp_SPCLDSG] ON
1307IF EXISTS (SELECT * FROM [dbo].[SPCLDSG])
1308 EXEC
1309 ('
1310 INSERT INTO [dbo].[tmp_SPCLDSG] ([APGRECNO],[UPDDATE],[USRN],[SPLDSGTEXT],[WHOLE_ROAD],[SPLDSGPRD],[LOCTEXT],[SEQNUM],[DESIGCODE],[STARTDATE],[ENDDATE],[START_TIME],[END_TIME],[GEOMETRY])
1311 SELECT [APGRECNO],[UPDDATE],[USRN],[SPLDSGTEXT],[WHOLE_ROAD],[SPLDSGPRD],[LOCTEXT],[SEQNUM],[DESIGCODE],[STARTDATE],[ENDDATE],[START_TIME],[END_TIME],[GEOMETRY] FROM [dbo].[SPCLDSG] TABLOCKX
1312 ')
1313IF @@ERROR<>0
1314Begin
1315 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1316 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.SPCLDSG')
1317END
1318GO
1319SET IDENTITY_INSERT [dbo].[tmp_SPCLDSG] OFF
1320
1321DROP TABLE [dbo].[SPCLDSG]
1322GO
1323IF @@ERROR<>0
1324Begin
1325 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1326 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.SPCLDSG')
1327END
1328GO
1329EXECUTE sp_rename N'[dbo].[tmp_SPCLDSG]', N'SPCLDSG', 'OBJECT'
1330
1331IF @@ERROR<>0
1332Begin
1333 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1334 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.SPCLDSG')
1335END
1336GO
1337
1338IF @@TRANCOUNT=0 BEGIN TRANSACTION
1339GO
1340SET ANSI_NULLS ON
1341GO
1342SET QUOTED_IDENTIFIER ON
1343GO
1344
1345CREATE TABLE [dbo].[tmp_STREET_SP] (
1346 [APGRECNO] [bigint] IDENTITY (1,1) NOT NULL,
1347 [UPDDATE] [datetime] NULL,
1348 [USRN] [varchar](8) COLLATE Latin1_General_CI_AS NULL,
1349 [DISPLAYFLG] [numeric](1,0) NULL,
1350 [SUB_TYPE] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
1351 [LENGTH] [numeric](10,3) NULL,
1352 [CLOSE_DATE] [date] NULL,
1353 [GEOMETRY] [geometry] NULL
1354) ON [PRIMARY]
1355
1356IF @@ERROR<>0
1357Begin
1358 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1359 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.STREET_SP')
1360END
1361GO
1362SET IDENTITY_INSERT [dbo].[tmp_STREET_SP] ON
1363IF EXISTS (SELECT * FROM [dbo].[STREET_SP])
1364 EXEC
1365 ('
1366 INSERT INTO [dbo].[tmp_STREET_SP] ([APGRECNO],[UPDDATE],[USRN],[DISPLAYFLG],[SUB_TYPE],[LENGTH],[CLOSE_DATE],[GEOMETRY])
1367 SELECT [APGRECNO],[UPDDATE],[USRN],[DISPLAYFLG],[SUB_TYPE],[LENGTH],[CLOSE_DATE],[GEOMETRY] FROM [dbo].[STREET_SP] TABLOCKX
1368 ')
1369IF @@ERROR<>0
1370Begin
1371 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1372 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.STREET_SP')
1373END
1374GO
1375SET IDENTITY_INSERT [dbo].[tmp_STREET_SP] OFF
1376
1377DROP TABLE [dbo].[STREET_SP]
1378GO
1379IF @@ERROR<>0
1380Begin
1381 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1382 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.STREET_SP')
1383END
1384GO
1385EXECUTE sp_rename N'[dbo].[tmp_STREET_SP]', N'STREET_SP', 'OBJECT'
1386
1387IF @@ERROR<>0
1388Begin
1389 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1390 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.STREET_SP')
1391END
1392GO
1393
1394IF @@TRANCOUNT=0 BEGIN TRANSACTION
1395GO
1396SET ANSI_NULLS ON
1397GO
1398SET QUOTED_IDENTIFIER ON
1399GO
1400
1401CREATE TABLE [dbo].[tmp_SUCCESSOR_XREF] (
1402 [UNIQUE_SUCCESSOR_XREF] [int] IDENTITY (1,1) NOT NULL,
1403 [PREDECESSOR] [float] NOT NULL,
1404 [SUCC_KEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
1405 [START_DATE] [datetime] NULL,
1406 [LAST_UPDATE_DATE] [datetime] NULL,
1407 [ENTRY_DATE] [datetime] NULL,
1408 [END_DATE] [datetime] NULL,
1409 [SUCCESSOR_TYPE] [smallint] NULL,
1410 [SUCCESSOR] [float] NULL,
1411 [LOCALONLY_SUCCKEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
1412 [localonly_uniqueid] [varchar](14) COLLATE Latin1_General_CI_AS NULL,
1413 [CAN_KEY] [varchar](14) COLLATE Latin1_General_CI_AS NULL
1414) ON [PRIMARY]
1415
1416IF @@ERROR<>0
1417Begin
1418 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1419 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.SUCCESSOR_XREF')
1420END
1421GO
1422SET IDENTITY_INSERT [dbo].[tmp_SUCCESSOR_XREF] ON
1423IF EXISTS (SELECT * FROM [dbo].[SUCCESSOR_XREF])
1424 EXEC
1425 ('
1426 INSERT INTO [dbo].[tmp_SUCCESSOR_XREF] ([UNIQUE_SUCCESSOR_XREF],[PREDECESSOR],[SUCC_KEY],[START_DATE],[LAST_UPDATE_DATE],[ENTRY_DATE],[END_DATE],[SUCCESSOR_TYPE],[SUCCESSOR],[LOCALONLY_SUCCKEY],[localonly_uniqueid],[CAN_KEY])
1427 SELECT [UNIQUE_SUCCESSOR_XREF],[PREDECESSOR],[SUCC_KEY],[START_DATE],[LAST_UPDATE_DATE],[ENTRY_DATE],[END_DATE],[SUCCESSOR_TYPE],[SUCCESSOR],[LOCALONLY_SUCCKEY],[localonly_uniqueid],[CAN_KEY] FROM [dbo].[SUCCESSOR_XREF] TABLOCKX
1428 ')
1429IF @@ERROR<>0
1430Begin
1431 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1432 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.SUCCESSOR_XREF')
1433END
1434GO
1435SET IDENTITY_INSERT [dbo].[tmp_SUCCESSOR_XREF] OFF
1436
1437DROP TABLE [dbo].[SUCCESSOR_XREF]
1438GO
1439IF @@ERROR<>0
1440Begin
1441 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1442 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.SUCCESSOR_XREF')
1443END
1444GO
1445EXECUTE sp_rename N'[dbo].[tmp_SUCCESSOR_XREF]', N'SUCCESSOR_XREF', 'OBJECT'
1446
1447IF @@ERROR<>0
1448Begin
1449 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1450 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.SUCCESSOR_XREF')
1451END
1452GO
1453
1454IF @@TRANCOUNT=0 BEGIN TRANSACTION
1455GO
1456SET ANSI_NULLS ON
1457GO
1458SET QUOTED_IDENTIFIER ON
1459GO
1460
1461CREATE TABLE [dbo].[tmp_TFI] (
1462 [TFI_UNIQUE] [int] IDENTITY (1,1) NOT NULL,
1463 [KEY_VALUE] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
1464 [ENTIRE_RECORD] [ntext] COLLATE Latin1_General_CI_AS NULL,
1465 [ENTRY_DATE] [datetime] NULL
1466) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
1467
1468IF @@ERROR<>0
1469Begin
1470 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1471 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TFI')
1472END
1473GO
1474SET IDENTITY_INSERT [dbo].[tmp_TFI] ON
1475IF EXISTS (SELECT * FROM [dbo].[TFI])
1476 EXEC
1477 ('
1478 INSERT INTO [dbo].[tmp_TFI] ([TFI_UNIQUE],[KEY_VALUE],[ENTIRE_RECORD],[ENTRY_DATE])
1479 SELECT [TFI_UNIQUE],[KEY_VALUE],[ENTIRE_RECORD],[ENTRY_DATE] FROM [dbo].[TFI] TABLOCKX
1480 ')
1481IF @@ERROR<>0
1482Begin
1483 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1484 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TFI')
1485END
1486GO
1487SET IDENTITY_INSERT [dbo].[tmp_TFI] OFF
1488
1489DROP TABLE [dbo].[TFI]
1490GO
1491IF @@ERROR<>0
1492Begin
1493 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1494 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TFI')
1495END
1496GO
1497EXECUTE sp_rename N'[dbo].[tmp_TFI]', N'TFI', 'OBJECT'
1498
1499IF @@ERROR<>0
1500Begin
1501 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1502 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TFI')
1503END
1504GO
1505
1506IF @@TRANCOUNT=0 BEGIN TRANSACTION
1507GO
1508SET ANSI_NULLS ON
1509GO
1510SET QUOTED_IDENTIFIER ON
1511GO
1512
1513CREATE TABLE [dbo].[tmp_TOWN] (
1514 [TOWN_REF] [int] IDENTITY (1,1) NOT NULL,
1515 [NAME] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
1516 [TYPE] [varchar](1) COLLATE Latin1_General_CI_AS NULL
1517) ON [PRIMARY]
1518
1519IF @@ERROR<>0
1520Begin
1521 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1522 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TOWN')
1523END
1524GO
1525SET IDENTITY_INSERT [dbo].[tmp_TOWN] ON
1526IF EXISTS (SELECT * FROM [dbo].[TOWN])
1527 EXEC
1528 ('
1529 INSERT INTO [dbo].[tmp_TOWN] ([TOWN_REF],[NAME],[TYPE])
1530 SELECT [TOWN_REF],[NAME],[TYPE] FROM [dbo].[TOWN] TABLOCKX
1531 ')
1532IF @@ERROR<>0
1533Begin
1534 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1535 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TOWN')
1536END
1537GO
1538SET IDENTITY_INSERT [dbo].[tmp_TOWN] OFF
1539
1540DROP TABLE [dbo].[TOWN]
1541GO
1542IF @@ERROR<>0
1543Begin
1544 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1545 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TOWN')
1546END
1547GO
1548EXECUTE sp_rename N'[dbo].[tmp_TOWN]', N'TOWN', 'OBJECT'
1549
1550IF @@ERROR<>0
1551Begin
1552 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1553 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TOWN')
1554END
1555GO
1556
1557IF @@TRANCOUNT=0 BEGIN TRANSACTION
1558GO
1559SET ANSI_NULLS ON
1560GO
1561SET QUOTED_IDENTIFIER ON
1562GO
1563
1564CREATE TABLE [dbo].[tmp_TRANSFER_FILE_IMAGE] (
1565 [TFI_UNIQUE] [int] IDENTITY (1,1) NOT NULL,
1566 [KEY_VALUE] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
1567 [ENTIRE_RECORD] [ntext] COLLATE Latin1_General_CI_AS NULL,
1568 [ENTRY_DATE] [datetime] NULL,
1569 [USERID] [varchar](50) COLLATE Latin1_General_CI_AS NULL
1570) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
1571
1572IF @@ERROR<>0
1573Begin
1574 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1575 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TRANSFER_FILE_IMAGE')
1576END
1577GO
1578SET IDENTITY_INSERT [dbo].[tmp_TRANSFER_FILE_IMAGE] ON
1579IF EXISTS (SELECT * FROM [dbo].[TRANSFER_FILE_IMAGE])
1580 EXEC
1581 ('
1582 INSERT INTO [dbo].[tmp_TRANSFER_FILE_IMAGE] ([TFI_UNIQUE],[KEY_VALUE],[ENTIRE_RECORD],[ENTRY_DATE],[USERID])
1583 SELECT [TFI_UNIQUE],[KEY_VALUE],[ENTIRE_RECORD],[ENTRY_DATE],[USERID] FROM [dbo].[TRANSFER_FILE_IMAGE] TABLOCKX
1584 ')
1585IF @@ERROR<>0
1586Begin
1587 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1588 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TRANSFER_FILE_IMAGE')
1589END
1590GO
1591SET IDENTITY_INSERT [dbo].[tmp_TRANSFER_FILE_IMAGE] OFF
1592
1593DROP TABLE [dbo].[TRANSFER_FILE_IMAGE]
1594GO
1595IF @@ERROR<>0
1596Begin
1597 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1598 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TRANSFER_FILE_IMAGE')
1599END
1600GO
1601EXECUTE sp_rename N'[dbo].[tmp_TRANSFER_FILE_IMAGE]', N'TRANSFER_FILE_IMAGE', 'OBJECT'
1602
1603IF @@ERROR<>0
1604Begin
1605 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1606 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TRANSFER_FILE_IMAGE')
1607END
1608GO
1609
1610IF @@TRANCOUNT=0 BEGIN TRANSACTION
1611GO
1612SET ANSI_NULLS ON
1613GO
1614SET QUOTED_IDENTIFIER ON
1615GO
1616
1617CREATE TABLE [dbo].[tmp_TRANSFER_FILE_IMAGE_ARCHIVE] (
1618 [TFI_ARCHIVE_UNIQUE] [int] IDENTITY (1,1) NOT NULL,
1619 [KEY_VALUE] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
1620 [VERSION_NUMBER] [int] NOT NULL,
1621 [ENTIRE_RECORD] [ntext] COLLATE Latin1_General_CI_AS NULL,
1622 [ENTRY_DATE] [datetime] NULL,
1623 [USERID] [varchar](50) COLLATE Latin1_General_CI_AS NULL
1624) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
1625
1626IF @@ERROR<>0
1627Begin
1628 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1629 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TRANSFER_FILE_IMAGE_ARCHIVE')
1630END
1631GO
1632SET IDENTITY_INSERT [dbo].[tmp_TRANSFER_FILE_IMAGE_ARCHIVE] ON
1633IF EXISTS (SELECT * FROM [dbo].[TRANSFER_FILE_IMAGE_ARCHIVE])
1634 EXEC
1635 ('
1636 INSERT INTO [dbo].[tmp_TRANSFER_FILE_IMAGE_ARCHIVE] ([TFI_ARCHIVE_UNIQUE],[KEY_VALUE],[VERSION_NUMBER],[ENTIRE_RECORD],[ENTRY_DATE],[USERID])
1637 SELECT [TFI_ARCHIVE_UNIQUE],[KEY_VALUE],[VERSION_NUMBER],[ENTIRE_RECORD],[ENTRY_DATE],[USERID] FROM [dbo].[TRANSFER_FILE_IMAGE_ARCHIVE] TABLOCKX
1638 ')
1639IF @@ERROR<>0
1640Begin
1641 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1642 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TRANSFER_FILE_IMAGE_ARCHIVE')
1643END
1644GO
1645SET IDENTITY_INSERT [dbo].[tmp_TRANSFER_FILE_IMAGE_ARCHIVE] OFF
1646
1647DROP TABLE [dbo].[TRANSFER_FILE_IMAGE_ARCHIVE]
1648GO
1649IF @@ERROR<>0
1650Begin
1651 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1652 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TRANSFER_FILE_IMAGE_ARCHIVE')
1653END
1654GO
1655EXECUTE sp_rename N'[dbo].[tmp_TRANSFER_FILE_IMAGE_ARCHIVE]', N'TRANSFER_FILE_IMAGE_ARCHIVE', 'OBJECT'
1656
1657IF @@ERROR<>0
1658Begin
1659 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1660 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter table dbo.TRANSFER_FILE_IMAGE_ARCHIVE')
1661END
1662GO
1663
1664IF @@TRANCOUNT=0 BEGIN TRANSACTION
1665GO
1666SET QUOTED_IDENTIFIER ON
1667GO
1668SET ANSI_NULLS ON
1669GO
1670CREATE VIEW [dbo].[ADDRESS_SEARCH]
1671AS
1672SELECT dbo.LAND_AND_PROPERTY_IDENTIFIER.UPRN, dbo.LAND_AND_PROPERTY_IDENTIFIER.SAO_FROM,
1673 dbo.LAND_AND_PROPERTY_IDENTIFIER.SAO_FROM_SUFF, dbo.LAND_AND_PROPERTY_IDENTIFIER.SAO_TO,
1674 dbo.LAND_AND_PROPERTY_IDENTIFIER.SAO_TO_SUFF, dbo.LAND_AND_PROPERTY_IDENTIFIER.SAO_NAME,
1675 dbo.LAND_AND_PROPERTY_IDENTIFIER.PAO_FROM, dbo.LAND_AND_PROPERTY_IDENTIFIER.PAO_FROM_SUFF,
1676 dbo.LAND_AND_PROPERTY_IDENTIFIER.PAO_TO, dbo.LAND_AND_PROPERTY_IDENTIFIER.PAO_TO_SUFF,
1677 dbo.LAND_AND_PROPERTY_IDENTIFIER.PAO_NAME, dbo.STREET.DESCRIPTION, dbo.TOWN.NAME AS TOWN_NAME,
1678 dbo.LAND_AND_PROPERTY_IDENTIFIER.POSTTOWN, dbo.LAND_AND_PROPERTY_IDENTIFIER.POSTCODE
1679FROM dbo.LAND_AND_PROPERTY_IDENTIFIER INNER JOIN
1680 dbo.STREET ON dbo.LAND_AND_PROPERTY_IDENTIFIER.USRN = dbo.STREET.USRN INNER JOIN
1681 dbo.TOWN ON dbo.STREET.TOWN_REF = dbo.TOWN.TOWN_REF
1682WHERE (dbo.LAND_AND_PROPERTY_IDENTIFIER.LOGICAL_STATUS = 1) AND (dbo.LAND_AND_PROPERTY_IDENTIFIER.PAO_NAME IS NULL) OR
1683 (dbo.LAND_AND_PROPERTY_IDENTIFIER.LOGICAL_STATUS = 1) AND (RTRIM(LTRIM(dbo.LAND_AND_PROPERTY_IDENTIFIER.PAO_NAME))
1684 <> 'STREET RECORD')
1685
1686GO
1687IF @@ERROR<>0
1688Begin
1689 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1690 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add view dbo.ADDRESS_SEARCH')
1691END
1692GO
1693SET QUOTED_IDENTIFIER OFF
1694GO
1695SET ANSI_NULLS ON
1696GO
1697
1698IF @@TRANCOUNT=0 BEGIN TRANSACTION
1699GO
1700SET QUOTED_IDENTIFIER ON
1701GO
1702SET ANSI_NULLS ON
1703GO
1704CREATE VIEW ALL_POSTALADDRESS AS
1705(
1706 SELECT dbo.BLPU.APGRECNO,
1707 dbo.BASIC_LAND_AND_PROPERTY_UNIT.XREF,
1708 dbo.BASIC_LAND_AND_PROPERTY_UNIT.YREF,
1709 dbo.BASIC_LAND_AND_PROPERTY_UNIT.UPRN AS UPRNs,
1710 dbo.LAND_AND_PROPERTY_IDENTIFIER.LPI_UNIQUE,
1711 dbo.LPI_POSTALADR.POSTALADDRESS,
1712 dbo.LAND_AND_PROPERTY_IDENTIFIER.POSTALLYADDRESSABLE,
1713 dbo.BASIC_LAND_AND_PROPERTY_UNIT.BLPU_CLASS,
1714 dbo.LAND_AND_PROPERTY_IDENTIFIER.LOGICAL_STATUS,
1715 dbo.BLPU.UPDDATE,
1716 dbo.BLPU.DISPLAYFLG,
1717 dbo.BLPU.LOG_STATUS,
1718 dbo.BLPU.PROP_TYPE,
1719 dbo.BLPU.GEOMETRY
1720
1721 FROM dbo.BASIC_LAND_AND_PROPERTY_UNIT INNER JOIN
1722 dbo.BLPU ON dbo.BASIC_LAND_AND_PROPERTY_UNIT.UPRN = dbo.BLPU.UPRN INNER JOIN
1723 dbo.LAND_AND_PROPERTY_IDENTIFIER ON dbo.BASIC_LAND_AND_PROPERTY_UNIT.UPRN = dbo.LAND_AND_PROPERTY_IDENTIFIER.UPRN INNER JOIN
1724 dbo.LPI_POSTALADR ON dbo.LAND_AND_PROPERTY_IDENTIFIER.LPI_UNIQUE = dbo.LPI_POSTALADR.LPI_UNIQUE
1725 );
1726GO
1727IF @@ERROR<>0
1728Begin
1729 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1730 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add view dbo.ALL_POSTALADDRESS')
1731END
1732GO
1733SET QUOTED_IDENTIFIER OFF
1734GO
1735SET ANSI_NULLS ON
1736GO
1737
1738IF @@TRANCOUNT=0 BEGIN TRANSACTION
1739GO
1740SET QUOTED_IDENTIFIER ON
1741GO
1742SET ANSI_NULLS ON
1743GO
1744CREATE VIEW [dbo].[POSTALADDRESS_VIEW]
1745AS (
1746 SELECT
1747 dbo.BLPU.APGRECNO,
1748 dbo.BASIC_LAND_AND_PROPERTY_UNIT.XREF,
1749 dbo.BASIC_LAND_AND_PROPERTY_UNIT.UPRN AS UPRNs,
1750 dbo.BASIC_LAND_AND_PROPERTY_UNIT.YREF,
1751 dbo.LPI_POSTALADR.POSTALADDRESS,
1752 dbo.LAND_AND_PROPERTY_IDENTIFIER.POSTALLYADDRESSABLE,
1753 dbo.BASIC_LAND_AND_PROPERTY_UNIT.BLPU_CLASS,
1754 dbo.LAND_AND_PROPERTY_IDENTIFIER.LOGICAL_STATUS,
1755 dbo.BLPU.UPDDATE,
1756 dbo.BLPU.DISPLAYFLG,
1757 dbo.BLPU.LOG_STATUS,
1758 dbo.BLPU.PROP_TYPE,
1759 dbo.BLPU.GEOMETRY
1760 FROM
1761 dbo.BASIC_LAND_AND_PROPERTY_UNIT INNER JOIN
1762 dbo.BLPU ON dbo.BASIC_LAND_AND_PROPERTY_UNIT.UPRN = dbo.BLPU.UPRN INNER JOIN
1763 dbo.LAND_AND_PROPERTY_IDENTIFIER ON dbo.BASIC_LAND_AND_PROPERTY_UNIT.UPRN = dbo.LAND_AND_PROPERTY_IDENTIFIER.UPRN INNER JOIN
1764 dbo.LPI_POSTALADR ON dbo.LAND_AND_PROPERTY_IDENTIFIER.LPI_UNIQUE = dbo.LPI_POSTALADR.LPI_UNIQUE
1765 );
1766
1767GO
1768IF @@ERROR<>0
1769Begin
1770 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1771 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add view dbo.POSTALADDRESS_VIEW')
1772END
1773GO
1774SET QUOTED_IDENTIFIER OFF
1775GO
1776SET ANSI_NULLS ON
1777GO
1778
1779SET ANSI_NULLS ON
1780GO
1781SET QUOTED_IDENTIFIER ON
1782GO
1783ALTER TABLE [dbo].[STREET] ADD
1784 CONSTRAINT [DF__STREET__release___656C112C] DEFAULT ((1)) FOR [release_number]
1785GO
1786
1787IF @@TRANCOUNT=0 BEGIN TRANSACTION
1788GO
1789SET QUOTED_IDENTIFIER ON
1790GO
1791SET ANSI_NULLS ON
1792GO
1793CREATE TRIGGER [dbo].[trg_BD_LPI]
1794ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER]
1795FOR DELETE
1796AS
1797 DECLARE @LPI_UNIQUE INTEGER;
1798BEGIN
1799 SELECT @LPI_UNIQUE=LPI_UNIQUE FROM DELETED;
1800PRINT @LPI_UNIQUE;
1801 DELETE FROM LPI_HISTORY WHERE LPI_UNIQUE =@LPI_UNIQUE;
1802 DELETE FROM LPI_POSTALADR WHERE LPI_UNIQUE =@LPI_UNIQUE;
1803 INSERT INTO LPI_DELETED SELECT * FROM DELETED WHERE LPI_UNIQUE=@LPI_UNIQUE;
1804END;
1805
1806GO
1807IF @@ERROR<>0
1808Begin
1809 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1810 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.LAND_AND_PROPERTY_IDENTIFIER.trg_BD_LPI')
1811END
1812GO
1813SET QUOTED_IDENTIFIER OFF
1814GO
1815SET ANSI_NULLS ON
1816GO
1817
1818IF @@TRANCOUNT=0 BEGIN TRANSACTION
1819GO
1820SET QUOTED_IDENTIFIER ON
1821GO
1822SET ANSI_NULLS ON
1823GO
1824create TRIGGER [dbo].[trgLPI]
1825ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER]
1826FOR INSERT
1827AS
1828DECLARE @LASTLPIKEY VARCHAR(14),@AUTHCODE VARCHAR(4),@TMPKEYVALUE VARCHAR(14),
1829@LPICOUNT INTEGER, @UPRN FLOAT, @LOGSTATUS VARCHAR(1), @CUSTODIANTWO INTEGER,
1830@LASTCANKEY VARCHAR(14),@LPIUNIQUE INTEGER, @CANKEY VARCHAR(14);
1831BEGIN
1832SELECT @AUTHCODE=RIGHT('0000'+cast(AUTH_CODE as varchar(5)), 4) from AUTHORITY WHERE USED_AUTH='Y';
1833SELECT @TMPKEYVALUE = INS.LPIKEY FROM INSERTED INS;
1834SELECT @UPRN = INS.UPRN FROM INSERTED INS;
1835SELECT @LOGSTATUS = INS.LOGICAL_STATUS FROM INSERTED INS;
1836SELECT @CUSTODIANTWO = INS.CUSTODIANTWO FROM INSERTED INS;
1837SELECT @LASTCANKEY = LAST_CAN_KEY FROM METADATA WHERE CUSTODIAN_CODE = @AUTHCODE;
1838SELECT @LPIUNIQUE= LPI_UNIQUE FROM INSERTED;
1839SELECT @CANKEY = CAN_KEY FROM INSERTED;
1840SELECT @LPICOUNT = COUNT(UPRN) FROM LAND_AND_PROPERTY_IDENTIFIER WHERE UPRN = @UPRN;
1841If (@LOGSTATUS = '5') and (@CANKEY is null)
1842BEGIN
1843 IF @LPICOUNT>0
1844 BEGIN
1845 IF @LASTCANKEY=''
1846 SET @LASTCANKEY = @AUTHCODE+'C'+'000000001'
1847 ELSE
1848 select @LASTCANKEY=@AUTHCODE+'C'+RIGHT('000000000'+CAST(cast(substring(LAST_CAN_KEY,6,14) as integer)+1 AS VARCHAR),9) from metadata;
1849 END
1850 ELSE
1851 SELECT @LASTCANKEY = CAN_KEY FROM BASIC_LAND_AND_PROPERTY_UNIT WHERE UPRN = @UPRN;
1852 UPDATE LAND_AND_PROPERTY_IDENTIFIER SET LPIKEY = @LASTCANKEY, CAN_KEY = @LASTCANKEY
1853 WHERE LPI_UNIQUE=@LPIUNIQUE;
1854 INSERT INTO LPI_CANDIDATE ( uprn, usrn, pao_from, pao_from_suff, pao_to, pao_to_suff, pao_name,
1855 sao_from, sao_from_suff, sao_to, sao_to_suff, sao_name, posttown, postcode, lpi_level, logical_status,
1856 start_date, end_date, entry_date, last_update_date, lpikey, postallyaddressable, custodianone, custodiantwo,
1857 can_key, language_code, request_source, candidate_flag,SUB_LOCALITY)
1858 SELECT uprn, usrn, pao_from, pao_from_suff, pao_to, pao_to_suff, pao_name,
1859 sao_from, sao_from_suff, sao_to, sao_to_suff, sao_name, posttown, postcode, lpi_level, logical_status,
1860 start_date, end_date, entry_date, last_update_date, @LASTCANKEY, postallyaddressable, custodianone, custodiantwo,
1861 @LASTCANKEY, language_code, @AUTHCODE, 'C01',SUB_LOCALITY FROM INSERTED;
1862 UPDATE METADATA SET last_can_key =@LASTCANKEY WHERE CUSTODIAN_CODE = @AUTHCODE;
1863END
1864ELSE IF @TMPKEYVALUE ='' or @tmpkeyvalue is null
1865BEGIN
1866 PRINT @AUTHCODE;
1867 SELECT @LASTLPIKEY = RIGHT('000000000'+CAST(LPI_UNIQUE AS VARCHAR),9) FROM INSERTED INS;
1868 PRINT @LASTLPIKEY;
1869 SET @TMPKEYVALUE=@AUTHCODE+'L'+@LASTLPIKEY;
1870 UPDATE LAND_AND_PROPERTY_IDENTIFIER SET LPIKEY = @TMPKEYVALUE WHERE LPI_UNIQUE =@LPIUNIQUE;
1871 PRINT @TMPKEYVALUE;
1872END
1873UPDATE METADATA SET LPIKEY = @TMPKEYVALUE WHERE CUSTODIAN_CODE = @AUTHCODE;
1874END;
1875
1876GO
1877IF @@ERROR<>0
1878Begin
1879 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1880 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.LAND_AND_PROPERTY_IDENTIFIER.trgLPI')
1881END
1882GO
1883SET QUOTED_IDENTIFIER OFF
1884GO
1885SET ANSI_NULLS ON
1886GO
1887
1888IF @@TRANCOUNT=0 BEGIN TRANSACTION
1889GO
1890SET QUOTED_IDENTIFIER ON
1891GO
1892SET ANSI_NULLS ON
1893GO
1894CREATE TRIGGER [dbo].[trg_BI_SUCCESSOR_XREF]
1895 ON [dbo].[SUCCESSOR_XREF]
1896FOR INSERT AS
1897DECLARE @LASTSUCCKEY VARCHAR(14),@AUTHCODE VARCHAR(4),@TMPKEYVALUE VARCHAR(14);
1898BEGIN
1899SELECT @TMPKEYVALUE = INS.SUCC_KEY FROM INSERTED INS;
1900PRINT @TMPKEYVALUE
1901IF @TMPKEYVALUE ='' or @TMPKEYVALUE is null
1902BEGIN
1903 SELECT @AUTHCODE=right('0000'+auth_CODE,4) from AUTHORITY WHERE USED_AUTH='Y';
1904 PRINT @AUTHCODE;
1905 SELECT @LASTSUCCKEY =RIGHT('000000000'+CAST(UNIQUE_SUCCESSOR_XREF AS VARCHAR),9) FROM INSERTED INS;
1906 PRINT @LASTSUCCKEY;
1907 SET @TMPKEYVALUE=@AUTHCODE+'S'+@LASTSUCCKEY;
1908 UPDATE SUCCESSOR_XREF SET SUCC_KEY = @TMPKEYVALUE WHERE UNIQUE_SUCCESSOR_XREF IN(SELECT UNIQUE_SUCCESSOR_XREF FROM INSERTED);
1909 PRINT @TMPKEYVALUE;
1910END
1911UPDATE METADATA SET SUCC_KEY = @TMPKEYVALUE WHERE CUSTODIAN_CODE = @AUTHCODE;
1912END;
1913
1914GO
1915IF @@ERROR<>0
1916Begin
1917 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
1918 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.SUCCESSOR_XREF.trg_BI_SUCCESSOR_XREF')
1919END
1920GO
1921SET QUOTED_IDENTIFIER OFF
1922GO
1923SET ANSI_NULLS ON
1924GO
1925
1926IF @@TRANCOUNT=0 BEGIN TRANSACTION
1927GO
1928SET QUOTED_IDENTIFIER ON
1929GO
1930SET ANSI_NULLS ON
1931GO
1932create TRIGGER [dbo].[TRG_AI_AU_BLPU_CANDIDATE]
1933ON [dbo].[BLPU_CANDIDATE]
1934for update
1935as
1936DECLARE
1937 @AUTHTYPE VARCHAR(14), @NEWUPRN FLOAT,@CUSTTWO INTEGER, @BLPUAUTHCODE VARCHAR(4), @UPRN FLOAT, @AUTHCODE VARCHAR(4),
1938 @CANDIDATEFLAG VARCHAR(3), @LINEOFTEXT VARCHAR(255), @CANKEY VARCHAR(14),@BLPUSTATEDATE VARCHAR(10),@STARTDATE VARCHAR(10),
1939 @ENDDATE VARCHAR(10), @LASTUPDATEDATE VARCHAR(10), @ENTRYDATE VARCHAR(10),@KEYVALUE VARCHAR(20);
1940BEGIN
1941 SET @NEWUPRN='';
1942print 'TRG_AI_AU_BLPU_CANDIDATE'
1943 select @AUTHTYPE = AUTHORITY_TYPE from AUTHORITY WHERE USED_AUTH='Y';
1944print @authtype;
1945 SELECT @CUSTTWO = CUSTODIANTWO FROM INSERTED;
1946print @CUSTTWO;
1947 SELECT @CANDIDATEFLAG = CANDIDATE_FLAG FROM INSERTED;
1948print @CANDIDATEFLAG;
1949 SELECT @CANKEY = CAN_KEY FROM INSERTED;
1950print 'cankey ='+@CANKEY;
1951 SELECT @UPRN = UPRN FROM INSERTED;
1952print @UPRN;
1953 select @AUTHCODE = AUTH_CODE from AUTHORITY WHERE USED_AUTH='Y';
1954print @AUTHCODE;
1955 select @BLPUSTATEDATE = CONVERT(VARCHAR(10), BLPU_STATE_DATE, 120) FROM INSERTED;
1956print @BLPUSTATEDATE;
1957 SELECT @STARTDATE = CONVERT(VARCHAR(10), START_DATE, 120) FROM INSERTED;
1958print @STARTDATE;
1959 SELECT @ENDDATE = CONVERT(VARCHAR(10), END_DATE, 120) FROM INSERTED;
1960print @ENDDATE;
1961 SELECT @LASTUPDATEDATE= CONVERT(VARCHAR(10), LAST_UPDATE_DATE, 120) FROM INSERTED;
1962print @LASTUPDATEDATE;
1963 SELECT @ENTRYDATE = CONVERT(VARCHAR(10), ENTRY_DATE, 120) FROM INSERTED;
1964print @ENTRYDATE;
1965 IF @CUSTTWO IS NOT NULL
1966 SELECT @BLPUAUTHCODE =CUSTODIAN_CODE FROM BASIC_LAND_AND_PROPERTY_UNIT WHERE UPRN = @UPRN;
1967 IF @BLPUAUTHCODE <> @AUTHCODE
1968 SET @NEWUPRN=@UPRN;
1969 IF @CANDIDATEFLAG = 'C01' and Len(@CANKEY)=14
1970 begin
1971 SET @LINEOFTEXT = '21,"C",';
1972 IF @CUSTTWO = 1
1973 bEGIN
1974 SET @NEWUPRN = @UPRN;
1975 SET @LINEOFTEXT = @LINEOFTEXT + Isnull(convert(varchar(100),cast(@NEWUPRN as decimal(12,0))),'');
1976 END;
1977 print @lineoftext;
1978 SELECT @LINEOFTEXT = @LINEOFTEXT + ',' + IsNull(LOGICAL_STATUS,'')+','+
1979 IsNull(CONVERT(VARCHAR(1),BLPUSTATE),'')+ ',"' + IsNull(BLPU_CLASS,'') +'",'+
1980 Isnull(convert(varchar(100),cast(parent_uprn as decimal(12,0))),'')
1981+','+Isnull(CONVERT(VARCHAR(10),XREF),'')+','+
1982Isnull(CONVERT(VARCHAR(10),YREF),'')+','+
1983 IsNull(convert(varchar(1),REP_POINT_CODE),'')
1984 +','+IsNull(convert(varchar(4),CUSTODIAN_CODE),'') + ','+
1985Isnull(@STARTDATE,'')+','+Isnull(@ENDDATE,'') +
1986 ','+ISnull(@LASTUPDATEDATE,'') +','+ Isnull(@ENTRYDATE,'')+',"'+
1987 IsNull(ORGANISATION,'')+'","'+IsNull(WARDCODE,'')+'","'+
1988IsNull(PARISHCODE,'')+'",'+ISnull(CONVERT(VARCHAR(4),CUSTODIANONE),'')
1989+','+Isnull(CONVERT(VARCHAR(4),CUSTODIANTWO),'')
1990 +',"'+IsNull(CAN_KEY,'')+'"' from Inserted;
1991 SET @KEYVALUE = '21' + Isnull(convert(varchar(100),cast(@NEWUPRN as decimal(12,0))),'')
1992 + IsNull(@CANKEY,'');
1993
1994 INSERT INTO CANDIDATE_TFI(KEY_VALUE,ENTIRE_RECORD,ENTRY_DATE)
1995 VALUES(@KEYVALUE,@LINEOFTEXT,GETDATE());
1996 end;
1997END;
1998
1999GO
2000IF @@ERROR<>0
2001Begin
2002 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2003 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.BLPU_CANDIDATE.TRG_AI_AU_BLPU_CANDIDATE')
2004END
2005GO
2006SET QUOTED_IDENTIFIER OFF
2007GO
2008SET ANSI_NULLS ON
2009GO
2010
2011IF @@TRANCOUNT=0 BEGIN TRANSACTION
2012GO
2013SET QUOTED_IDENTIFIER ON
2014GO
2015SET ANSI_NULLS ON
2016GO
2017Create TRIGGER [dbo].[TRG_BI_BU_BLPU_CANDIDATE]
2018ON [dbo].[BLPU_CANDIDATE]
2019FOR INSERT,update
2020AS
2021DECLARE
2022 @LASTCANKEY VARCHAR(14),@AUTHCODE VARCHAR(4),@CANKEY VARCHAR(14),@CANFLAG VARCHAR(3),
2023 @BLPUCANID FLOAT;
2024BEGIN
2025SELECT @AUTHCODE=right('0000'+auth_CODE,4) from AUTHORITY WHERE USED_AUTH='Y';
2026SELECT @CANKEY = CAN_KEY FROM INSERTED;
2027SELECT @BLPUCANID = BLPU_CANDIDATE_ID FROM INSERTED;
2028SELECT @CANFLAG = CANDIDATE_FLAG FROM INSERTED;
2029SELECT @LASTCANKEY = LAST_CAN_KEY FROM METADATA WHERE CUSTODIAN_CODE = @AUTHCODE;
2030print 'TRG_BI_BU_BLPU_CANDIDATE';
2031 IF (@CANFLAG = 'C01') and (len(@cankey)<>14 or @CANKEY is null)
2032 BEGIN
2033 IF (@LASTCANKEY ='') or (@LASTCANKEY IS NULL)
2034 SET @LASTCANKEY=@AUTHCODE+'C'+'000000001';
2035 ELSE
2036 select @LASTCANKEY=@AUTHCODE+'C'+RIGHT('000000000'+CAST(cast(substring(LAST_CAN_KEY,6,14) as integer)+1 AS VARCHAR),9) from metadata;
2037 UPDATE BLPU_CANDIDATE SET CAN_KEY = @LASTCANKEY WHERE BLPU_CANDIDATE_ID = @BLPUCANID;
2038 UPDATE METADATA SET last_can_key =@LASTCANKEY WHERE CUSTODIAN_CODE = @AUTHCODE;
2039 END;
2040END;
2041
2042GO
2043IF @@ERROR<>0
2044Begin
2045 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2046 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.BLPU_CANDIDATE.TRG_BI_BU_BLPU_CANDIDATE')
2047END
2048GO
2049SET QUOTED_IDENTIFIER OFF
2050GO
2051SET ANSI_NULLS ON
2052GO
2053
2054IF @@TRANCOUNT=0 BEGIN TRANSACTION
2055GO
2056SET QUOTED_IDENTIFIER ON
2057GO
2058SET ANSI_NULLS ON
2059GO
2060create TRIGGER [dbo].[trg_AI_BLPU]
2061ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT]
2062FOR INSERT
2063AS
2064DECLARE
2065 @AUTHTYPE VARCHAR(2),@LOCALONLYPRIORITY VARCHAR(3),@DATEVALUE VARCHAR(15),@KEYVALUE VARCHAR(100),
2066 @LASTCANKEY VARCHAR(14), @AUTHCODE VARCHAR(4), @NEWUPRN FLOAT, @NEWBLPUCLASS VARCHAR(4),
2067 @NEWLOGSTATUS VARCHAR(1), @NEWORG varchar(100);
2068BEGIN
2069 SELECT @AUTHCODE=right('0000'+auth_CODE,4) from AUTHORITY WHERE USED_AUTH='Y';
2070 select @AUTHTYPE= AUTHORITY_TYPE from AUTHORITY WHERE USED_AUTH='Y';
2071 SELECT @NEWUPRN=UPRN FROM INSERTED;
2072 SELECT @NEWBLPUCLASS=BLPU_CLASS FROM INSERTED;
2073 SELECT @NEWORG=ORGANISATION FROM INSERTED;
2074 SELECT @NEWLOGSTATUS = LOGICAL_STATUS FROM INSERTED;
2075 SELECT @LASTCANKEY = CAN_KEY FROM INSERTED;
2076 SELECT @LOCALONLYPRIORITY = LOCALONLY_PRIORITY FROM INSERTED;
2077 IF CHARINDEX(@AUTHTYPE,'S')>0
2078 If (@NEWBLPUCLASS <>'' AND @NEWLOGSTATUS ='5' AND @LASTCANKEY <>'')
2079 BEGIN
2080 INSERT INTO BLPU_CLASSIFICATION(UPRN,CLASS_KEY,blpu_class,CLASS_SCHEME,START_DATE,ENTRY_DATE,LAST_UPDATE_DATE,LOCALONLY_PRIORITY)
2081 VALUES(@NEWUPRN,@LASTCANKEY,@NEWBLPUCLASS,'Scottish Gazetteer Conventions V1.0',getdate(),getdate(),getdate(),@LOCALONLYPRIORITY);
2082 END;
2083 ELSE IF (@NEWBLPUCLASS <> '' AND @NEWLOGSTATUS <>'5')
2084 INSERT INTO BLPU_CLASSIFICATION(UPRN,blpu_class,CLASS_SCHEME,START_DATE,ENTRY_DATE,LAST_UPDATE_DATE,LOCALONLY_PRIORITY)
2085 VALUES(@NEWUPRN,@NEWBLPUCLASS,'Scottish Gazetteer Conventions V1.0',getdate(),getdate(),getdate(),@LOCALONLYPRIORITY);
2086
2087 If (@NEWORG <>'' AND @NEWLOGSTATUS ='5' AND @LASTCANKEY <>'')
2088 INSERT INTO ORGANISATION(UPRN,ORG_KEY,ORGANISATION,START_DATE,ENTRY_DATE,LAST_UPDATE_DATE,LOCALONLY_PRIORITY)
2089 VALUES(@NEWUPRN,@LASTCANKEY,@NEWORG,getdate(),getdate(),getdate(),@LOCALONLYPRIORITY);
2090 ELSE If (@NEWORG <>'' AND @NEWLOGSTATUS <>'5')
2091 INSERT INTO ORGANISATION(UPRN,ORGANISATION,START_DATE,ENTRY_DATE,LAST_UPDATE_DATE,LOCALONLY_PRIORITY)
2092 VALUES(@NEWUPRN, @NEWORG, getdate(), getdate() ,getdate(),@LOCALONLYPRIORITY);
2093END;
2094
2095GO
2096IF @@ERROR<>0
2097Begin
2098 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2099 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.BASIC_LAND_AND_PROPERTY_UNIT.trg_AI_BLPU')
2100END
2101GO
2102SET QUOTED_IDENTIFIER OFF
2103GO
2104SET ANSI_NULLS ON
2105GO
2106
2107IF @@TRANCOUNT=0 BEGIN TRANSACTION
2108GO
2109SET QUOTED_IDENTIFIER ON
2110GO
2111SET ANSI_NULLS ON
2112GO
2113CREATE TRIGGER [dbo].[trg_BD_BLPU]
2114ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT]
2115for DELETE
2116as
2117DECLARE
2118 @UPRNCOUNT INTEGER, @TFICOUNT INTEGER, @UPRN FLOAT,
2119 @LOGSTATUS VARCHAR(1),@CANKEY VARCHAR(14);
2120BEGIN
2121 SELECT @UPRN = UPRN FROM DELETED;
2122 SELECT @LOGSTATUS = LOGICAL_STATUS FROM DELETED;
2123 SELECT @CANKEY = CAN_KEY FROM DELETED;
2124 DELETE FROM BLPU_HISTORY WHERE UPRN=@UPRN;
2125 SELECT @UPRNCOUNT = COUNT(UPRN) FROM BLPU_DELETED WHERE UPRN = @UPRN;
2126 IF @LOGSTATUS = '5'
2127 BEGIN
2128 DELETE FROM BLPU_CANDIDATE WHERE CAN_KEY = @CANKEY;
2129 SELECT @TFICOUNT=COUNT(TFI_UNIQUE) FROM CANDIDATE_TFI WHERE
2130 KEY_VALUE LIKE '24%'+@CANKEY+'%';
2131 IF @TFICOUNT=0
2132 DELETE FROM CANDIDATE_TFI WHERE KEY_VALUE LIKE '21%'+@CANKEY+'%';
2133 END;
2134 if @UPRNCOUNT=0
2135 INSERT INTO BLPU_DELETED SELECT * FROM DELETED WHERE UPRN = @UPRN;
2136END;
2137
2138GO
2139IF @@ERROR<>0
2140Begin
2141 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2142 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.BASIC_LAND_AND_PROPERTY_UNIT.trg_BD_BLPU')
2143END
2144GO
2145SET QUOTED_IDENTIFIER OFF
2146GO
2147SET ANSI_NULLS ON
2148GO
2149
2150IF @@TRANCOUNT=0 BEGIN TRANSACTION
2151GO
2152SET QUOTED_IDENTIFIER ON
2153GO
2154SET ANSI_NULLS ON
2155GO
2156CREATE TRIGGER [dbo].[trg_BI_BLPU]
2157ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT]
2158FOR INSERT
2159AS
2160DECLARE
2161 @AUTHTYPE VARCHAR(14), @NEWORGKEY VARCHAR(14), @NEWBLPUCLASSKEY VARCHAR(14),
2162 @LINEOFTEXT VARCHAR(255), @DATEVALUE VARCHAR(15), @KEYVALUE VARCHAR(100),@BLPUCLASS VARCHAR(4),
2163 @UPRN FLOAT, @ORG VARCHAR(100),@NEWBLPUCLASS VARCHAR(4), @NEWORG VARCHAR(100),
2164 @NEWLOGSTATUS VARCHAR(1), @LOCALONLYPRIORITY VARCHAR(3), @CUSTODIANTWO INTEGER,
2165 @LASTCANKEY VARCHAR(14),@AUTHCODE VARCHAR(4), @CANKEY VARCHAR(14);
2166BEGIN
2167 select @AUTHTYPE = AUTHORITY_TYPE from AUTHORITY WHERE USED_AUTH='Y';
2168 SELECT @AUTHCODE=AUTH_CODE FROM AUTHORITY WHERE USED_AUTH = 'Y';
2169 SELECT @DATEVALUE = CONVERT(VARCHAR(10),GETDATE(),120);
2170 SELECT @UPRN = UPRN FROM INSERTED;
2171 SELECT @BLPUCLASS = BLPU_CLASS FROM INSERTED;
2172 SELECT @ORG= ORGANISATION FROM INSERTED;
2173 SELECT @NEWLOGSTATUS = LOGICAL_STATUS FROM INSERTED;
2174 SELECT @CUSTODIANTWO = CUSTODIANTWO FROM INSERTED;
2175 SELECT @CANKEY = CAN_KEY FROM INSERTED;
2176 SELECT @LASTCANKEY = LAST_CAN_KEY FROM METADATA WHERE CUSTODIAN_CODE = @AUTHCODE;
2177 If (@NEWLOGSTATUS = '5' AND @CANKEY IS NULL)
2178 BEGIN
2179 IF @LASTCANKEY=''
2180 SET @LASTCANKEY = @AUTHCODE+'C'+'000000001'
2181 ELSE
2182 select @LASTCANKEY=@AUTHCODE+'C'+RIGHT('000000000'+CAST(cast(substring(LAST_CAN_KEY,6,14) as integer)+1 AS VARCHAR),9) from metadata;
2183 INSERT INTO BLPU_CANDIDATE (uprn, custodian_code, xref, yref, logical_status, rep_point_code, start_date,
2184 end_date, entry_date, last_update_date, blpustate, blpu_state_date, blpu_class, parent_uprn, organisation,
2185 wardcode, parishcode, custodianone, custodiantwo, can_key, request_source, candidate_flag)
2186 SELECT uprn, custodian_code, xref, yref,LOGICAL_STATUS, rep_point_code, start_date,
2187 end_date, entry_date, last_update_date, blpustate, blpu_state_date, blpu_class, parent_uprn, organisation, wardcode, parishcode,
2188 snnflag, custodiantwo, @LASTCANKEY, @AUTHCODE , 'C01' FROM INSERTED;
2189 UPDATE BASIC_LAND_AND_PROPERTY_UNIT SET CAN_KEY = @LASTCANKEY WHERE UPRN = @UPRN;
2190 UPDATE METADATA SET last_can_key = @LASTCANKEY WHERE CUSTODIAN_CODE = @AUTHCODE;
2191 End;
2192 End;
2193
2194GO
2195IF @@ERROR<>0
2196Begin
2197 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2198 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.BASIC_LAND_AND_PROPERTY_UNIT.trg_BI_BLPU')
2199END
2200GO
2201SET QUOTED_IDENTIFIER OFF
2202GO
2203SET ANSI_NULLS ON
2204GO
2205
2206IF @@TRANCOUNT=0 BEGIN TRANSACTION
2207GO
2208SET QUOTED_IDENTIFIER ON
2209GO
2210SET ANSI_NULLS ON
2211GO
2212create TRIGGER [dbo].[TRG_AI_AU_LPI_CANDIDATE]
2213ON [dbo].[LPI_CANDIDATE]
2214for update,INSERT
2215AS
2216DECLARE
2217 @AUTHTYPE VARCHAR(14), @NEWUPRN FLOAT,@CUSTTWO INTEGER, @BLPUAUTHCODE VARCHAR(4), @UPRN FLOAT, @AUTHCODE VARCHAR(4),
2218 @CANDIDATEFLAG VARCHAR(3), @LINEOFTEXT VARCHAR(255), @CANKEY VARCHAR(14),@BLPUSTATEDATE VARCHAR(10),@STARTDATE VARCHAR(10),
2219 @ENDDATE VARCHAR(10), @LASTUPDATEDATE VARCHAR(10), @ENTRYDATE VARCHAR(10),@KEYVALUE VARCHAR(20);
2220BEGIN
2221 SET @NEWUPRN='';
2222 SELECT @CUSTTWO = CUSTODIANTWO FROM INSERTED;
2223 SELECT @CANDIDATEFLAG = CANDIDATE_FLAG FROM INSERTED;
2224 SELECT @CANKEY = CAN_KEY FROM INSERTED;
2225 SELECT @UPRN = UPRN FROM INSERTED;
2226 SELECT @STARTDATE = CONVERT(VARCHAR(10), START_DATE, 120) FROM INSERTED;
2227 SELECT @ENDDATE = CONVERT(VARCHAR(10), END_DATE, 120) FROM INSERTED;
2228 SELECT @LASTUPDATEDATE= CONVERT(VARCHAR(10), LAST_UPDATE_DATE, 120) FROM INSERTED;
2229 SELECT @ENTRYDATE = CONVERT(VARCHAR(10), ENTRY_DATE, 120) FROM INSERTED;
2230 IF @CANDIDATEFLAG = 'C01' and Len(@CANKEY)=14
2231 begin
2232 SET @LINEOFTEXT = '24,"C",';
2233 IF @CUSTTWO IS NOT NULL
2234 BEGIN
2235 SET @NEWUPRN = @UPRN;
2236 set @LINEOFTEXT= @LINEOFTEXT + Isnull(convert(varchar(100),cast(@NEWUPRN as decimal(12,0))),'');
2237 END;
2238 SELECT @LINEOFTEXT = @LINEOFTEXT + ',,"' + ISNULL(LANGUAGE_code,'')
2239 +'",'+ ISNULL(LOGICAL_STATUS,'') + ',' + IsNull(@STARTDATE,'') +','+
2240 ISnull(@ENDDATE,'') +','+ IsNull(@ENTRYDATE,'') +',' +
2241 IsNull(@LASTUPDATEDATE,'') +','+ IsNull(CONVERT(VARCHAR(4),SAO_FROM),'')
2242 +',"'+ISNULL(SAO_FROM_SUFF,'') + '",'+ IsNull(CONVERT(VARCHAR(4),SAO_TO),'')
2243 +',"'+ ISNULL(SAO_TO_SUFF,'')
2244 +'","'+ISNULL(sao_name,'') +'",'+ IsNull(CONVERT(VARCHAR(4),PAO_FROM),'')
2245 +',"'+ISNULL(PAO_FROM_SUFF,'') + '",'+
2246 IsNull(CONVERT(VARCHAR(4),PAO_TO),'') +',"'+ISNULL(PAO_TO_SUFF,'')
2247 +'","'+IsNull(Pao_name,'')+'",'+ IsNull(convert(varchar(8),USRN),'')+',"'+
2248 IsNull(LPI_LEVEL,'')+'","'+IsNull(POSTALLYADDRESSABLE,'')+'","'+IsNull(POSTCODE,'')+
2249 '","'+IsNull(POSTTOWN,'')+'","'+IsNull(OFFICIAL_flag,'')+'",'+ IsNull(convert(varchar(1),CUSTODIANONE),'')+
2250 ','+IsNull(Convert(varchar(1),CUSTODIANTWO),'') +',"'+IsNull(CAN_KEY,'')+'"' from Inserted;
2251 SET @KEYVALUE = '24' + Isnull(convert(varchar(100),cast(@NEWUPRN as decimal(12,0))),'') + IsNull(@CANKEY,'');
2252 INSERT INTO CANDIDATE_TFI(KEY_VALUE,ENTIRE_RECORD,ENTRY_DATE)
2253 VALUES(@KEYVALUE,@LINEOFTEXT,getDATE());
2254 End;
2255END;
2256
2257GO
2258IF @@ERROR<>0
2259Begin
2260 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2261 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.LPI_CANDIDATE.TRG_AI_AU_LPI_CANDIDATE')
2262END
2263GO
2264SET QUOTED_IDENTIFIER OFF
2265GO
2266SET ANSI_NULLS ON
2267GO
2268
2269IF @@TRANCOUNT=0 BEGIN TRANSACTION
2270GO
2271SET QUOTED_IDENTIFIER ON
2272GO
2273SET ANSI_NULLS ON
2274GO
2275CREATE TRIGGER [dbo].[trg_BD_LPI_CANDIDATE]
2276ON [dbo].[LPI_CANDIDATE]
2277FOR DELETE
2278AS
2279DECLARE
2280 @CANDIDATECOUNT INTEGER,@LPICANDIDATEID INTEGER;
2281BEGIN
2282 SELECT @LPICANDIDATEID = LPI_CANDIDATE_ID FROM LPI_CANDIDATE;
2283 SELECT @CANDIDATECOUNT = COUNT(LPI_CANDIDATE_ID) FROM LPI_CANDIDATE_ARCHIVE WHERE LPI_CANDIDATE_ID = @LPICANDIDATEID;
2284 if @CANDIDATECOUNT=0
2285 BEGIN
2286 INSERT INTO LPI_CANDIDATE_ARCHIVE SELECT * FROM LPI_CANDIDATE WHERE LPI_CANDIDATE_ID = @LPICANDIDATEID;
2287 END;
2288END;
2289
2290GO
2291IF @@ERROR<>0
2292Begin
2293 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2294 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.LPI_CANDIDATE.trg_BD_LPI_CANDIDATE')
2295END
2296GO
2297SET QUOTED_IDENTIFIER OFF
2298GO
2299SET ANSI_NULLS ON
2300GO
2301
2302IF @@TRANCOUNT=0 BEGIN TRANSACTION
2303GO
2304SET QUOTED_IDENTIFIER ON
2305GO
2306SET ANSI_NULLS ON
2307GO
2308create TRIGGER [dbo].[TRG_BI_LPI_CANDIDATE]
2309ON [dbo].[LPI_CANDIDATE]
2310FOR INSERT , update
2311AS
2312DECLARE
2313 @LASTCANKEY VARCHAR(14),@AUTHCODE VARCHAR(4),@CANKEY VARCHAR(14),
2314 @CANFLAG VARCHAR(3),@UPRN float,
2315 @LPIUNIQUE INTEGER;
2316BEGIN
2317 SELECT @AUTHCODE=right('0000'+auth_CODE,4) from AUTHORITY WHERE USED_AUTH='Y';
2318 SELECT @CANKEY = CAN_KEY FROM INSERTED;
2319 SELECT @CANFLAG = CANDIDATE_FLAG FROM INSERTED;
2320 SELECT @LPIUNIQUE= LPI_CANDIDATE_ID FROM INSERTED;
2321 SELECT @UPRN = UPRN FROM INSERTED;
2322 SELECT @LASTCANKEY = LAST_CAN_KEY FROM METADATA WHERE CUSTODIAN_CODE = @AUTHCODE;
2323 IF @CANFLAG = 'C01' and (len(@cankey)<>14 or @CAnKey is null)
2324 BEGIN
2325 IF (@LASTCANKEY ='') or (@LastCankey is null)
2326 SET @LASTCANKEY=@AUTHCODE+'C'+'000000001';
2327 ELSE
2328 select @LASTCANKEY=@AUTHCODE+'C'+RIGHT('000000000'+CAST(cast(substring(LAST_CAN_KEY,6,14)
2329 as integer)+1 AS VARCHAR),9) from metadata;
2330 print 'LastCanley='+ @lastCanKey;
2331 ;;SET @CANKEY = @LASTCANKEY;
2332 UPDATE LPI_CANDIDATE SET CAN_KEY = @LASTCANKEY,LPIKEY = @LASTCANKEY WHERE LPI_CANDIDATE_ID = @LPIUNIQUE;
2333 UPDATE METADATA SET last_can_key =@LASTCANKEY WHERE CUSTODIAN_CODE = @AUTHCODE;
2334 END;
2335END;
2336
2337GO
2338IF @@ERROR<>0
2339Begin
2340 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2341 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.LPI_CANDIDATE.TRG_BI_LPI_CANDIDATE')
2342END
2343GO
2344SET QUOTED_IDENTIFIER OFF
2345GO
2346SET ANSI_NULLS ON
2347GO
2348
2349IF @@TRANCOUNT=0 BEGIN TRANSACTION
2350GO
2351SET QUOTED_IDENTIFIER ON
2352GO
2353SET ANSI_NULLS ON
2354GO
2355CREATE TRIGGER [dbo].[trg_BI_ORG_KEY]
2356ON [dbo].[ORGANISATION]
2357for INSERT
2358as
2359DECLARE
2360 @LASTORGKEY VARCHAR(14),@AUTHCODE VARCHAR(4), @ORGKEY VARCHAR(14),
2361 @LOCALONLYPRIORITY VARCHAR(3), @LASTCANKEY VARCHAR(14),
2362 @UNIQUEORG INTEGER,@UPRN FLOAT, @ORGANISATION VARCHAR(100);
2363BEGIN
2364 SELECT @AUTHCODE=right('0000'+auth_CODE,4) from AUTHORITY WHERE USED_AUTH='Y';
2365 SELECT @LASTCANKEY = LAST_CAN_KEY FROM METADATA WHERE CUSTODIAN_CODE = @AUTHCODE;
2366 SELECT @LOCALONLYPRIORITY = LOCALONLY_PRIORITY FROM INSERTED;
2367 SELECT @UNIQUEORG = UNIQUE_ORG FROM INSERTED;
2368 SELECT @ORGKEY = ORG_KEY FROM INSERTED;
2369 SELECT @UPRN = UPRN FROM INSERTED;
2370 SELECT @ORGANISATION= ORGANISATION FROM INSERTED;
2371 If charindex(@LOCALONLYPRIORITY,'C')>0 AND @ORGKEY=''
2372 BEGIN
2373 IF @LASTCANKEY =''
2374 SET @LASTCANKEY=@AUTHCODE+'C'+'000000001'
2375 else
2376 select @LASTCANKEY=@AUTHCODE+'C'+RIGHT('000000000'+CAST(cast(substring(LAST_CAN_KEY,6,14) as integer)+1 AS VARCHAR),9)
2377 from metadata;
2378 UPDATE ORGANISATION SET ORG_KEY = @LASTCANKEY WHERE UNIQUE_ORG = @UNIQUEORG;
2379 INSERT INTO BLPU_CANDIDATE (uprn, custodian_code, xref, yref, logical_status, rep_point_code, start_date,
2380 end_date, entry_date, last_update_date, blpustate, blpu_state_date,parent_uprn, organisation, custodiantwo,
2381 can_key, request_source, candidate_flag)
2382 SELECT UPRN,CUSTODIAN_CODE,XREF,YREF,'5',REP_POINT_CODE,START_DATE,END_DATE, ENTRY_DATE, LAST_UPDATE_DATE,
2383 BLPUSTATE, BLPU_STATE_DATE,PARENT_UPRN,@ORGANISATION,1,@LASTCANKEY,CUSTODIAN_CODE,'C01' FROM BASIC_LAND_AND_PROPERTY_UNIT
2384 WHERE UPRN = @UPRN;
2385 UPdate metadata set LAST_CAN_KEY=@LASTCANKEY WHERE CUSTODIAN_CODE = @AUTHCODE;
2386 END;
2387 elsE IF @ORGKEY ='' or @orgkey is null
2388 SELECT @LASTORGKEY =RIGHT('000000000'+CAST(UNIQUE_ORG AS VARCHAR),9) FROM INSERTED;
2389 SET @LASTORGKEY =@AUTHCODE+'O'+@LASTORGKEY;
2390 UPDATE ORGANISATION SET ORG_KEY = @LASTORGKEY WHERE UNIQUE_ORG =@UNIQUEORG;
2391 UPDATE METADATA SET org_key = @LASTORGKEY WHERE CUSTODIAN_CODE = @AUTHCODE;
2392
2393END;
2394
2395GO
2396IF @@ERROR<>0
2397Begin
2398 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2399 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.ORGANISATION.trg_BI_ORG_KEY')
2400END
2401GO
2402SET QUOTED_IDENTIFIER OFF
2403GO
2404SET ANSI_NULLS ON
2405GO
2406
2407IF @@TRANCOUNT=0 BEGIN TRANSACTION
2408GO
2409SET QUOTED_IDENTIFIER ON
2410GO
2411SET ANSI_NULLS ON
2412GO
2413CREATE TRIGGER [dbo].[trg_BI_BLPU_CLASSIFICATION]
2414ON [dbo].[BLPU_CLASSIFICATION]
2415for INSERT
2416AS
2417DECLARE
2418 @LASTBLPUCLASSKEY VARCHAR(14),@AUTHCODE VARCHAR(4), @BLPUclassKEY VARCHAR(14),
2419 @LOCALONLYPRIORITY VARCHAR(3), @LASTCANKEY VARCHAR(14), @UNIQUEBLPUCLASS INTEGER,
2420 @UPRN FLOAT, @BLPUCLASS VARCHAR(100);
2421
2422BEGIN
2423 SELECT @AUTHCODE=right('0000'+auth_CODE,4) from AUTHORITY WHERE USED_AUTH='Y';
2424 SELECT @LASTCANKEY = LAST_CAN_KEY FROM METADATA WHERE CUSTODIAN_CODE = @AUTHCODE;
2425 SELECT @LOCALONLYPRIORITY = LOCALONLY_PRIORITY FROM INSERTED;
2426 SELECT @UNIQUEBLPUCLASS = UNIQUE_BLPUCLASS FROM INSERTED;
2427 SELECT @BLPUCLASSKEY = CLASS_KEY FROM INSERTED;
2428 SELECT @UPRN = UPRN FROM INSERTED;
2429 SELECT @BLPUCLASS = BLPU_CLASS FROM INSERTED;
2430
2431 If charindex(@LOCALONLYPRIORITY,'C')>0 AND @BLPUCLASSKEY=''
2432 BEGIN
2433 IF @LASTCANKEY =''
2434 SET @LASTCANKEY=@AUTHCODE+'C'+'000000001'
2435 else
2436 select @LASTCANKEY=@AUTHCODE+'C'+RIGHT('000000000'+CAST(cast(substring(LAST_CAN_KEY,6,14) as integer)+1 AS VARCHAR),9)
2437 from metadata;
2438 UPDATE BLPU_CLASSIFICATION SET CLASS_KEY = @LASTCANKEY WHERE UNIQUE_BLPUCLASS = @UNIQUEBLPUCLASS;
2439 INSERT INTO BLPU_CANDIDATE (uprn, custodian_code, xref, yref, logical_status, rep_point_code, start_date,
2440 end_date, entry_date, last_update_date, blpustate, blpu_state_date,parent_uprn, BLPU_CLASS, custodiantwo,
2441 can_key, request_source, candidate_flag)
2442 SELECT UPRN,CUSTODIAN_CODE,XREF,YREF,'5',REP_POINT_CODE,START_DATE,END_DATE, ENTRY_DATE, LAST_UPDATE_DATE,
2443 BLPUSTATE, BLPU_STATE_DATE,PARENT_UPRN,@BLPUCLASS,1,@LASTCANKEY,CUSTODIAN_CODE,'C01' FROM BASIC_LAND_AND_PROPERTY_UNIT
2444 WHERE UPRN = @UPRN;
2445 UPdate metadata set LAST_CAN_KEY=@LASTCANKEY WHERE CUSTODIAN_CODE = @AUTHCODE;
2446 END;
2447 ELSE IF @BLPUCLASSKEY='' or @blpuclasskey is null
2448 SELECT @LASTBLPUCLASSKEY =RIGHT('000000000'+CAST(UNIQUE_BLPUCLASS AS VARCHAR),9) FROM INSERTED INS;
2449 PRINT @LASTBLPUCLASSKEY;
2450 SET @LASTBLPUCLASSKEY =@AUTHCODE+'B'+@LASTBLPUCLASSKEY ;
2451 UPDATE BLPU_CLASSIFICATION SET CLASS_KEY = @LASTBLPUCLASSKEY WHERE UNIQUE_BLPUCLASS=@UNIQUEBLPUCLASS;
2452 UPdate metadata set LAST_CAN_KEY=@LASTBLPUCLASSKEY WHERE CUSTODIAN_CODE = @AUTHCODE;
2453
2454END;
2455
2456GO
2457IF @@ERROR<>0
2458Begin
2459 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2460 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add trigger dbo.BLPU_CLASSIFICATION.trg_BI_BLPU_CLASSIFICATION')
2461END
2462GO
2463SET QUOTED_IDENTIFIER OFF
2464GO
2465SET ANSI_NULLS ON
2466GO
2467
2468IF @@TRANCOUNT=0 BEGIN TRANSACTION
2469GO
2470SET ANSI_NULLS ON
2471GO
2472SET QUOTED_IDENTIFIER ON
2473GO
2474if not exists (select * from sys.objects where [name] = N'PKLPI_HISTORY' and [type] = 'PK')
2475ALTER TABLE [dbo].[LPI_HISTORY] ADD
2476 CONSTRAINT [PKLPI_HISTORY] PRIMARY KEY CLUSTERED
2477 (
2478 [LPI_UNIQUE],[VERSION_NO]
2479 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2480GO
2481
2482IF @@ERROR<>0
2483Begin
2484 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2485 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.LPI_HISTORY.PKLPI_HISTORY')
2486END
2487GO
2488IF @@TRANCOUNT=0 BEGIN TRANSACTION
2489GO
2490SET ANSI_NULLS ON
2491GO
2492SET QUOTED_IDENTIFIER ON
2493GO
2494if not exists (select * from sys.objects where [name] = N'pkLPIPostalAdr' and [type] = 'PK')
2495ALTER TABLE [dbo].[LPI_POSTALADR] ADD
2496 CONSTRAINT [pkLPIPostalAdr] PRIMARY KEY CLUSTERED
2497 (
2498 [LPI_UNIQUE]
2499 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2500GO
2501
2502IF @@ERROR<>0
2503Begin
2504 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2505 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.LPI_POSTALADR.pkLPIPostalAdr')
2506END
2507GO
2508IF @@TRANCOUNT=0 BEGIN TRANSACTION
2509GO
2510SET ANSI_NULLS ON
2511GO
2512SET QUOTED_IDENTIFIER ON
2513GO
2514if not exists (select * from sys.objects where [name] = N'PK_OWE_HISTORY' and [type] = 'PK')
2515ALTER TABLE [dbo].[OWE_HISTORY] ADD
2516 CONSTRAINT [PK_OWE_HISTORY] PRIMARY KEY CLUSTERED
2517 (
2518 [ESU_ID],[SEQUENCE_NUMBER],[AUTH_CODE],[VERSION_NUMBER]
2519 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2520GO
2521
2522IF @@ERROR<>0
2523Begin
2524 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2525 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.OWE_HISTORY.PK_OWE_HISTORY')
2526END
2527GO
2528IF @@TRANCOUNT=0 BEGIN TRANSACTION
2529GO
2530SET ANSI_NULLS ON
2531GO
2532SET QUOTED_IDENTIFIER ON
2533GO
2534if not exists (select * from sys.objects where [name] = N'PK_ORG_DELETED' and [type] = 'PK')
2535ALTER TABLE [dbo].[ORGANISATION_DELETED] ADD
2536 CONSTRAINT [PK_ORG_DELETED] PRIMARY KEY CLUSTERED
2537 (
2538 [UNIQUE_ORG]
2539 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2540GO
2541
2542IF @@ERROR<>0
2543Begin
2544 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2545 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ORGANISATION_DELETED.PK_ORG_DELETED')
2546END
2547GO
2548IF @@TRANCOUNT=0 BEGIN TRANSACTION
2549GO
2550SET ANSI_NULLS ON
2551GO
2552SET QUOTED_IDENTIFIER ON
2553GO
2554if not exists (select * from sys.objects where [name] = N'PK_ONEWAYSTREETEXEMPT' and [type] = 'PK')
2555ALTER TABLE [dbo].[ONEWAYSTREETEXEMPT] ADD
2556 CONSTRAINT [PK_ONEWAYSTREETEXEMPT] PRIMARY KEY CLUSTERED
2557 (
2558 [ESU_ID],[SEQUENCE_NUMBER],[AUTH_CODE]
2559 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2560GO
2561
2562IF @@ERROR<>0
2563Begin
2564 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2565 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ONEWAYSTREETEXEMPT.PK_ONEWAYSTREETEXEMPT')
2566END
2567GO
2568IF @@TRANCOUNT=0 BEGIN TRANSACTION
2569GO
2570SET ANSI_NULLS ON
2571GO
2572SET QUOTED_IDENTIFIER ON
2573GO
2574if not exists (select * from sys.objects where [name] = N'PKNODE_LOOKUP' and [type] = 'PK')
2575ALTER TABLE [dbo].[NODE_LOOKUP] ADD
2576 CONSTRAINT [PKNODE_LOOKUP] PRIMARY KEY CLUSTERED
2577 (
2578 [NODE_ID],[STREET_ESU_ID]
2579 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2580GO
2581
2582IF @@ERROR<>0
2583Begin
2584 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2585 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.NODE_LOOKUP.PKNODE_LOOKUP')
2586END
2587GO
2588IF @@TRANCOUNT=0 BEGIN TRANSACTION
2589GO
2590SET ANSI_NULLS ON
2591GO
2592SET QUOTED_IDENTIFIER ON
2593GO
2594if not exists (select * from sys.objects where [name] = N'PK_NODE_CNS' and [type] = 'PK')
2595ALTER TABLE [dbo].[NODE] ADD
2596 CONSTRAINT [PK_NODE_CNS] PRIMARY KEY CLUSTERED
2597 (
2598 [APGRECNO]
2599 ) ON [PRIMARY];
2600GO
2601
2602IF @@ERROR<>0
2603Begin
2604 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2605 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.NODE.PK_NODE_CNS')
2606END
2607GO
2608IF @@TRANCOUNT=0 BEGIN TRANSACTION
2609GO
2610SET ANSI_NULLS ON
2611GO
2612SET QUOTED_IDENTIFIER ON
2613GO
2614if not exists (select * from sys.objects where [name] = N'pkMetadata' and [type] = 'PK')
2615ALTER TABLE [dbo].[METADATA] ADD
2616 CONSTRAINT [pkMetadata] PRIMARY KEY CLUSTERED
2617 (
2618 [CUSTODIAN_CODE]
2619 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2620GO
2621
2622IF @@ERROR<>0
2623Begin
2624 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2625 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.METADATA.pkMetadata')
2626END
2627GO
2628IF @@TRANCOUNT=0 BEGIN TRANSACTION
2629GO
2630SET ANSI_NULLS ON
2631GO
2632SET QUOTED_IDENTIFIER ON
2633GO
2634if not exists (select * from sys.objects where [name] = N'PK_ORGANISATION' and [type] = 'PK')
2635ALTER TABLE [dbo].[ORGANISATION] ADD
2636 CONSTRAINT [PK_ORGANISATION] PRIMARY KEY CLUSTERED
2637 (
2638 [UNIQUE_ORG]
2639 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2640GO
2641
2642IF @@ERROR<>0
2643Begin
2644 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2645 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ORGANISATION.PK_ORGANISATION')
2646END
2647GO
2648IF @@TRANCOUNT=0 BEGIN TRANSACTION
2649GO
2650SET ANSI_NULLS ON
2651GO
2652SET QUOTED_IDENTIFIER ON
2653GO
2654if not exists (select * from sys.objects where [name] = N'PK_HEIGHTWIDTHWEIGHT' and [type] = 'PK')
2655ALTER TABLE [dbo].[HEIGHTWIDTHWEIGHT] ADD
2656 CONSTRAINT [PK_HEIGHTWIDTHWEIGHT] PRIMARY KEY CLUSTERED
2657 (
2658 [USRN],[HWW_SEQUENCE_NO],[AUTH_CODE]
2659 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2660GO
2661
2662IF @@ERROR<>0
2663Begin
2664 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2665 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.HEIGHTWIDTHWEIGHT.PK_HEIGHTWIDTHWEIGHT')
2666END
2667GO
2668IF @@TRANCOUNT=0 BEGIN TRANSACTION
2669GO
2670SET ANSI_NULLS ON
2671GO
2672SET QUOTED_IDENTIFIER ON
2673GO
2674if not exists (select * from sys.objects where [name] = N'PK_HWW_CNS' and [type] = 'PK')
2675ALTER TABLE [dbo].[HWW] ADD
2676 CONSTRAINT [PK_HWW_CNS] PRIMARY KEY CLUSTERED
2677 (
2678 [APGRECNO]
2679 ) ON [PRIMARY];
2680GO
2681
2682IF @@ERROR<>0
2683Begin
2684 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2685 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.HWW.PK_HWW_CNS')
2686END
2687GO
2688IF @@TRANCOUNT=0 BEGIN TRANSACTION
2689GO
2690SET ANSI_NULLS ON
2691GO
2692SET QUOTED_IDENTIFIER ON
2693GO
2694if not exists (select * from sys.objects where [name] = N'PK_HWW_HISTORY' and [type] = 'PK')
2695ALTER TABLE [dbo].[HWW_HISTORY] ADD
2696 CONSTRAINT [PK_HWW_HISTORY] PRIMARY KEY CLUSTERED
2697 (
2698 [USRN],[HWW_SEQUENCE_NO],[AUTH_CODE],[VERSION_NUMBER]
2699 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2700GO
2701
2702IF @@ERROR<>0
2703Begin
2704 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2705 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.HWW_HISTORY.PK_HWW_HISTORY')
2706END
2707GO
2708IF @@TRANCOUNT=0 BEGIN TRANSACTION
2709GO
2710SET ANSI_NULLS ON
2711GO
2712SET QUOTED_IDENTIFIER ON
2713GO
2714if not exists (select * from sys.objects where [name] = N'PKEXPORT_DATES' and [type] = 'PK')
2715ALTER TABLE [dbo].[EXPORT_DATES] ADD
2716 CONSTRAINT [PKEXPORT_DATES] PRIMARY KEY CLUSTERED
2717 (
2718 [EXPORT_ID]
2719 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2720GO
2721
2722IF @@ERROR<>0
2723Begin
2724 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2725 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.EXPORT_DATES.PKEXPORT_DATES')
2726END
2727GO
2728IF @@TRANCOUNT=0 BEGIN TRANSACTION
2729GO
2730SET ANSI_NULLS ON
2731GO
2732SET QUOTED_IDENTIFIER ON
2733GO
2734if not exists (select * from sys.objects where [name] = N'pkEXPORTID_OldTFI' and [type] = 'PK')
2735ALTER TABLE [dbo].[EXPORTDATES_TFIOLD] ADD
2736 CONSTRAINT [pkEXPORTID_OldTFI] PRIMARY KEY CLUSTERED
2737 (
2738 [EXPORT_ID]
2739 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2740GO
2741
2742IF @@ERROR<>0
2743Begin
2744 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2745 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.EXPORTDATES_TFIOLD.pkEXPORTID_OldTFI')
2746END
2747GO
2748IF @@TRANCOUNT=0 BEGIN TRANSACTION
2749GO
2750SET ANSI_NULLS ON
2751GO
2752SET QUOTED_IDENTIFIER ON
2753GO
2754if not exists (select * from sys.objects where [name] = N'PKGGP_LINK' and [type] = 'PK')
2755ALTER TABLE [dbo].[GGP_LINK] ADD
2756 CONSTRAINT [PKGGP_LINK] PRIMARY KEY CLUSTERED
2757 (
2758 [STREET_BLPU_KEY]
2759 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2760GO
2761
2762IF @@ERROR<>0
2763Begin
2764 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2765 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.GGP_LINK.PKGGP_LINK')
2766END
2767GO
2768IF @@TRANCOUNT=0 BEGIN TRANSACTION
2769GO
2770SET ANSI_NULLS ON
2771GO
2772SET QUOTED_IDENTIFIER ON
2773GO
2774if not exists (select * from sys.objects where [name] = N'PK_LPI_CANDIDATE' and [type] = 'PK')
2775ALTER TABLE [dbo].[LPI_CANDIDATE] ADD
2776 CONSTRAINT [PK_LPI_CANDIDATE] PRIMARY KEY CLUSTERED
2777 (
2778 [LPI_CANDIDATE_ID]
2779 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2780GO
2781
2782IF @@ERROR<>0
2783Begin
2784 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2785 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.LPI_CANDIDATE.PK_LPI_CANDIDATE')
2786END
2787GO
2788IF @@TRANCOUNT=0 BEGIN TRANSACTION
2789GO
2790SET ANSI_NULLS ON
2791GO
2792SET QUOTED_IDENTIFIER ON
2793GO
2794if not exists (select * from sys.objects where [name] = N'PK_LPI_CANDIDATE_ARCHIVE' and [type] = 'PK')
2795ALTER TABLE [dbo].[LPI_CANDIDATE_ARCHIVE] ADD
2796 CONSTRAINT [PK_LPI_CANDIDATE_ARCHIVE] PRIMARY KEY CLUSTERED
2797 (
2798 [LPI_CANDIDATE_ID]
2799 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2800GO
2801
2802IF @@ERROR<>0
2803Begin
2804 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2805 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.LPI_CANDIDATE_ARCHIVE.PK_LPI_CANDIDATE_ARCHIVE')
2806END
2807GO
2808IF @@TRANCOUNT=0 BEGIN TRANSACTION
2809GO
2810SET ANSI_NULLS ON
2811GO
2812SET QUOTED_IDENTIFIER ON
2813GO
2814if not exists (select * from sys.objects where [name] = N'PK_LPI_DELETED' and [type] = 'PK')
2815ALTER TABLE [dbo].[LPI_DELETED] ADD
2816 CONSTRAINT [PK_LPI_DELETED] PRIMARY KEY CLUSTERED
2817 (
2818 [LPI_UNIQUE]
2819 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2820GO
2821
2822IF @@ERROR<>0
2823Begin
2824 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2825 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.LPI_DELETED.PK_LPI_DELETED')
2826END
2827GO
2828IF @@TRANCOUNT=0 BEGIN TRANSACTION
2829GO
2830SET ANSI_NULLS ON
2831GO
2832SET QUOTED_IDENTIFIER ON
2833GO
2834if not exists (select * from sys.objects where [name] = N'PK_IMPEXP_ERRORCODE' and [type] = 'PK')
2835ALTER TABLE [dbo].[ImpExpErrorCodes] ADD
2836 CONSTRAINT [PK_IMPEXP_ERRORCODE] PRIMARY KEY CLUSTERED
2837 (
2838 [ERRORCODE]
2839 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2840GO
2841
2842IF @@ERROR<>0
2843Begin
2844 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2845 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ImpExpErrorCodes.PK_IMPEXP_ERRORCODE')
2846END
2847GO
2848IF @@TRANCOUNT=0 BEGIN TRANSACTION
2849GO
2850SET ANSI_NULLS ON
2851GO
2852SET QUOTED_IDENTIFIER ON
2853GO
2854if not exists (select * from sys.objects where [name] = N'PKLPI' and [type] = 'PK')
2855ALTER TABLE [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ADD
2856 CONSTRAINT [PKLPI] PRIMARY KEY CLUSTERED
2857 (
2858 [LPI_UNIQUE]
2859 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2860GO
2861
2862IF @@ERROR<>0
2863Begin
2864 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2865 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.LAND_AND_PROPERTY_IDENTIFIER.PKLPI')
2866END
2867GO
2868IF @@TRANCOUNT=0 BEGIN TRANSACTION
2869GO
2870SET ANSI_NULLS ON
2871GO
2872SET QUOTED_IDENTIFIER ON
2873GO
2874if not exists (select * from sys.objects where [name] = N'PKLOCALITY' and [type] = 'PK')
2875ALTER TABLE [dbo].[LOCALITY] ADD
2876 CONSTRAINT [PKLOCALITY] PRIMARY KEY CLUSTERED
2877 (
2878 [LOC_REF]
2879 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2880GO
2881
2882IF @@ERROR<>0
2883Begin
2884 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2885 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.LOCALITY.PKLOCALITY')
2886END
2887GO
2888IF @@TRANCOUNT=0 BEGIN TRANSACTION
2889GO
2890SET ANSI_NULLS ON
2891GO
2892SET QUOTED_IDENTIFIER ON
2893GO
2894if not exists (select * from sys.objects where [name] = N'PKPARISH' and [type] = 'PK')
2895ALTER TABLE [dbo].[PARISH] ADD
2896 CONSTRAINT [PKPARISH] PRIMARY KEY CLUSTERED
2897 (
2898 [PARISHCODE]
2899 ) ON [PRIMARY];
2900GO
2901
2902IF @@ERROR<>0
2903Begin
2904 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2905 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.PARISH.PKPARISH')
2906END
2907GO
2908IF @@TRANCOUNT=0 BEGIN TRANSACTION
2909GO
2910SET ANSI_NULLS ON
2911GO
2912SET QUOTED_IDENTIFIER ON
2913GO
2914if not exists (select * from sys.objects where [name] = N'PK_STREET_SP_CNS' and [type] = 'PK')
2915ALTER TABLE [dbo].[STREET_SP] ADD
2916 CONSTRAINT [PK_STREET_SP_CNS] PRIMARY KEY CLUSTERED
2917 (
2918 [APGRECNO]
2919 ) ON [PRIMARY];
2920GO
2921
2922IF @@ERROR<>0
2923Begin
2924 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2925 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.STREET_SP.PK_STREET_SP_CNS')
2926END
2927GO
2928IF @@TRANCOUNT=0 BEGIN TRANSACTION
2929GO
2930SET ANSI_NULLS ON
2931GO
2932SET QUOTED_IDENTIFIER ON
2933GO
2934if not exists (select * from sys.objects where [name] = N'PK_SUCCESSOR_XREF' and [type] = 'PK')
2935ALTER TABLE [dbo].[SUCCESSOR_XREF] ADD
2936 CONSTRAINT [PK_SUCCESSOR_XREF] PRIMARY KEY CLUSTERED
2937 (
2938 [UNIQUE_SUCCESSOR_XREF]
2939 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2940GO
2941
2942IF @@ERROR<>0
2943Begin
2944 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2945 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.SUCCESSOR_XREF.PK_SUCCESSOR_XREF')
2946END
2947GO
2948IF @@TRANCOUNT=0 BEGIN TRANSACTION
2949GO
2950SET ANSI_NULLS ON
2951GO
2952SET QUOTED_IDENTIFIER ON
2953GO
2954if not exists (select * from sys.objects where [name] = N'PK_SUCCESSOR_XREF_DEL' and [type] = 'PK')
2955ALTER TABLE [dbo].[SUCCESSOR_XREF_DELETED] ADD
2956 CONSTRAINT [PK_SUCCESSOR_XREF_DEL] PRIMARY KEY CLUSTERED
2957 (
2958 [UNIQUE_SUCCESSOR_XREF]
2959 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2960GO
2961
2962IF @@ERROR<>0
2963Begin
2964 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2965 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.SUCCESSOR_XREF_DELETED.PK_SUCCESSOR_XREF_DEL')
2966END
2967GO
2968IF @@TRANCOUNT=0 BEGIN TRANSACTION
2969GO
2970SET ANSI_NULLS ON
2971GO
2972SET QUOTED_IDENTIFIER ON
2973GO
2974if not exists (select * from sys.objects where [name] = N'PKSTREETDESC_HISTORY' and [type] = 'PK')
2975ALTER TABLE [dbo].[STREET_DESC_HISTORY] ADD
2976 CONSTRAINT [PKSTREETDESC_HISTORY] PRIMARY KEY CLUSTERED
2977 (
2978 [USRN],[LANGUAGE_CODE],[VERSION_NUMBER]
2979 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
2980GO
2981
2982IF @@ERROR<>0
2983Begin
2984 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
2985 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.STREET_DESC_HISTORY.PKSTREETDESC_HISTORY')
2986END
2987GO
2988IF @@TRANCOUNT=0 BEGIN TRANSACTION
2989GO
2990SET ANSI_NULLS ON
2991GO
2992SET QUOTED_IDENTIFIER ON
2993GO
2994if not exists (select * from sys.objects where [name] = N'PKSTREET_DESC' and [type] = 'PK')
2995ALTER TABLE [dbo].[STREET_DESCRIPTOR] ADD
2996 CONSTRAINT [PKSTREET_DESC] PRIMARY KEY CLUSTERED
2997 (
2998 [USRN],[LANGUAGE_CODE]
2999 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3000GO
3001
3002IF @@ERROR<>0
3003Begin
3004 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3005 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.STREET_DESCRIPTOR.PKSTREET_DESC')
3006END
3007GO
3008IF @@TRANCOUNT=0 BEGIN TRANSACTION
3009GO
3010SET ANSI_NULLS ON
3011GO
3012SET QUOTED_IDENTIFIER ON
3013GO
3014if not exists (select * from sys.objects where [name] = N'PKSTREET_HISTORY' and [type] = 'PK')
3015ALTER TABLE [dbo].[STREET_HISTORY] ADD
3016 CONSTRAINT [PKSTREET_HISTORY] PRIMARY KEY CLUSTERED
3017 (
3018 [USRN],[VERSION_NO]
3019 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3020GO
3021
3022IF @@ERROR<>0
3023Begin
3024 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3025 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.STREET_HISTORY.PKSTREET_HISTORY')
3026END
3027GO
3028IF @@TRANCOUNT=0 BEGIN TRANSACTION
3029GO
3030SET ANSI_NULLS ON
3031GO
3032SET QUOTED_IDENTIFIER ON
3033GO
3034if not exists (select * from sys.objects where [name] = N'PK_TRANSFER_FILE_ARCHIVE' and [type] = 'PK')
3035ALTER TABLE [dbo].[TRANSFER_FILE_IMAGE_ARCHIVE] ADD
3036 CONSTRAINT [PK_TRANSFER_FILE_ARCHIVE] PRIMARY KEY CLUSTERED
3037 (
3038 [TFI_ARCHIVE_UNIQUE]
3039 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3040GO
3041
3042IF @@ERROR<>0
3043Begin
3044 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3045 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.TRANSFER_FILE_IMAGE_ARCHIVE.PK_TRANSFER_FILE_ARCHIVE')
3046END
3047GO
3048IF @@TRANCOUNT=0 BEGIN TRANSACTION
3049GO
3050SET ANSI_NULLS ON
3051GO
3052SET QUOTED_IDENTIFIER ON
3053GO
3054if not exists (select * from sys.objects where [name] = N'PK_USERID' and [type] = 'PK')
3055ALTER TABLE [dbo].[USERTABLE] ADD
3056 CONSTRAINT [PK_USERID] PRIMARY KEY CLUSTERED
3057 (
3058 [USERID]
3059 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3060GO
3061
3062IF @@ERROR<>0
3063Begin
3064 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3065 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.USERTABLE.PK_USERID')
3066END
3067GO
3068IF @@TRANCOUNT=0 BEGIN TRANSACTION
3069GO
3070SET ANSI_NULLS ON
3071GO
3072SET QUOTED_IDENTIFIER ON
3073GO
3074if not exists (select * from sys.objects where [name] = N'PKWARD' and [type] = 'PK')
3075ALTER TABLE [dbo].[WARD] ADD
3076 CONSTRAINT [PKWARD] PRIMARY KEY CLUSTERED
3077 (
3078 [WARDCODE]
3079 ) ON [PRIMARY];
3080GO
3081
3082IF @@ERROR<>0
3083Begin
3084 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3085 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.WARD.PKWARD')
3086END
3087GO
3088IF @@TRANCOUNT=0 BEGIN TRANSACTION
3089GO
3090SET ANSI_NULLS ON
3091GO
3092SET QUOTED_IDENTIFIER ON
3093GO
3094if not exists (select * from sys.objects where [name] = N'pkTFIOld' and [type] = 'PK')
3095ALTER TABLE [dbo].[TFI] ADD
3096 CONSTRAINT [pkTFIOld] PRIMARY KEY CLUSTERED
3097 (
3098 [TFI_UNIQUE]
3099 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3100GO
3101
3102IF @@ERROR<>0
3103Begin
3104 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3105 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.TFI.pkTFIOld')
3106END
3107GO
3108IF @@TRANCOUNT=0 BEGIN TRANSACTION
3109GO
3110SET ANSI_NULLS ON
3111GO
3112SET QUOTED_IDENTIFIER ON
3113GO
3114if not exists (select * from sys.objects where [name] = N'PK_TOWN' and [type] = 'PK')
3115ALTER TABLE [dbo].[TOWN] ADD
3116 CONSTRAINT [PK_TOWN] PRIMARY KEY CLUSTERED
3117 (
3118 [TOWN_REF]
3119 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3120GO
3121
3122IF @@ERROR<>0
3123Begin
3124 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3125 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.TOWN.PK_TOWN')
3126END
3127GO
3128IF @@TRANCOUNT=0 BEGIN TRANSACTION
3129GO
3130SET ANSI_NULLS ON
3131GO
3132SET QUOTED_IDENTIFIER ON
3133GO
3134if not exists (select * from sys.objects where [name] = N'PK_TRANSFER_FILE' and [type] = 'PK')
3135ALTER TABLE [dbo].[TRANSFER_FILE_IMAGE] ADD
3136 CONSTRAINT [PK_TRANSFER_FILE] PRIMARY KEY CLUSTERED
3137 (
3138 [TFI_UNIQUE]
3139 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3140GO
3141
3142IF @@ERROR<>0
3143Begin
3144 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3145 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.TRANSFER_FILE_IMAGE.PK_TRANSFER_FILE')
3146END
3147GO
3148IF @@TRANCOUNT=0 BEGIN TRANSACTION
3149GO
3150SET ANSI_NULLS ON
3151GO
3152SET QUOTED_IDENTIFIER ON
3153GO
3154if not exists (select * from sys.objects where [name] = N'PKPROW' and [type] = 'PK')
3155ALTER TABLE [dbo].[PUBLIC_RIGHT_OF_WAY] ADD
3156 CONSTRAINT [PKPROW] PRIMARY KEY CLUSTERED
3157 (
3158 [PROW_ID]
3159 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3160GO
3161
3162IF @@ERROR<>0
3163Begin
3164 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3165 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.PUBLIC_RIGHT_OF_WAY.PKPROW')
3166END
3167GO
3168IF @@TRANCOUNT=0 BEGIN TRANSACTION
3169GO
3170SET ANSI_NULLS ON
3171GO
3172SET QUOTED_IDENTIFIER ON
3173GO
3174if not exists (select * from sys.objects where [name] = N'PK_REINST_DESIG_HISTORY' and [type] = 'PK')
3175ALTER TABLE [dbo].[REINST_DESIG_HISTORY] ADD
3176 CONSTRAINT [PK_REINST_DESIG_HISTORY] PRIMARY KEY CLUSTERED
3177 (
3178 [USRN],[STREET_REINST_TYPE_SEQ_NUM],[STREET_REINST_TYPE_CODE],[REINST_VERSION_NO],[AUTH_CODE]
3179 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3180GO
3181
3182IF @@ERROR<>0
3183Begin
3184 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3185 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.REINST_DESIG_HISTORY.PK_REINST_DESIG_HISTORY')
3186END
3187GO
3188IF @@TRANCOUNT=0 BEGIN TRANSACTION
3189GO
3190SET ANSI_NULLS ON
3191GO
3192SET QUOTED_IDENTIFIER ON
3193GO
3194if not exists (select * from sys.objects where [name] = N'PK_REINST_DESIGNATION' and [type] = 'PK')
3195ALTER TABLE [dbo].[REINST_DESIGNATION] ADD
3196 CONSTRAINT [PK_REINST_DESIGNATION] PRIMARY KEY CLUSTERED
3197 (
3198 [USRN],[STREET_REINST_TYPE_SEQ_NUM],[AUTH_CODE]
3199 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3200GO
3201
3202IF @@ERROR<>0
3203Begin
3204 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3205 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.REINST_DESIGNATION.PK_REINST_DESIGNATION')
3206END
3207GO
3208IF @@TRANCOUNT=0 BEGIN TRANSACTION
3209GO
3210SET ANSI_NULLS ON
3211GO
3212SET QUOTED_IDENTIFIER ON
3213GO
3214if not exists (select * from sys.objects where [name] = N'PK__POLICEBE__1FF2510737A5467C' and [type] = 'PK')
3215ALTER TABLE [dbo].[POLICEBEATCODE] ADD
3216 CONSTRAINT [PK__POLICEBE__1FF2510737A5467C] PRIMARY KEY CLUSTERED
3217 (
3218 [POLICEBEATCODEID]
3219 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3220GO
3221
3222IF @@ERROR<>0
3223Begin
3224 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3225 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.POLICEBEATCODE.PK__POLICEBE__1FF2510737A5467C')
3226END
3227GO
3228IF @@TRANCOUNT=0 BEGIN TRANSACTION
3229GO
3230SET ANSI_NULLS ON
3231GO
3232SET QUOTED_IDENTIFIER ON
3233GO
3234if not exists (select * from sys.objects where [name] = N'PK_POLICEFORCEAREA' and [type] = 'PK')
3235ALTER TABLE [dbo].[POLICEFORCEAREA] ADD
3236 CONSTRAINT [PK_POLICEFORCEAREA] PRIMARY KEY CLUSTERED
3237 (
3238 [POLICEFORCEAREAID]
3239 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3240GO
3241
3242IF @@ERROR<>0
3243Begin
3244 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3245 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.POLICEFORCEAREA.PK_POLICEFORCEAREA')
3246END
3247GO
3248IF @@TRANCOUNT=0 BEGIN TRANSACTION
3249GO
3250SET ANSI_NULLS ON
3251GO
3252SET QUOTED_IDENTIFIER ON
3253GO
3254if not exists (select * from sys.objects where [name] = N'PK_PROVENANCE' and [type] = 'PK')
3255ALTER TABLE [dbo].[PROVENANCE] ADD
3256 CONSTRAINT [PK_PROVENANCE] PRIMARY KEY CLUSTERED
3257 (
3258 [UPRN],[PROV_CODE]
3259 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3260GO
3261
3262IF @@ERROR<>0
3263Begin
3264 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3265 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.PROVENANCE.PK_PROVENANCE')
3266END
3267GO
3268IF @@TRANCOUNT=0 BEGIN TRANSACTION
3269GO
3270SET ANSI_NULLS ON
3271GO
3272SET QUOTED_IDENTIFIER ON
3273GO
3274if not exists (select * from sys.objects where [name] = N'PK_SPECIAL_DESIGNATION' and [type] = 'PK')
3275ALTER TABLE [dbo].[SPECIAL_DESIGNATION] ADD
3276 CONSTRAINT [PK_SPECIAL_DESIGNATION] PRIMARY KEY CLUSTERED
3277 (
3278 [USRN],[STREET_SPECIAL_DESIG_NUM],[AUTH_CODE]
3279 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3280GO
3281
3282IF @@ERROR<>0
3283Begin
3284 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3285 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.SPECIAL_DESIGNATION.PK_SPECIAL_DESIGNATION')
3286END
3287GO
3288IF @@TRANCOUNT=0 BEGIN TRANSACTION
3289GO
3290SET ANSI_NULLS ON
3291GO
3292SET QUOTED_IDENTIFIER ON
3293GO
3294if not exists (select * from sys.objects where [name] = N'PKSTREET' and [type] = 'PK')
3295ALTER TABLE [dbo].[STREET] ADD
3296 CONSTRAINT [PKSTREET] PRIMARY KEY CLUSTERED
3297 (
3298 [USRN]
3299 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3300GO
3301
3302IF @@ERROR<>0
3303Begin
3304 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3305 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.STREET.PKSTREET')
3306END
3307GO
3308IF @@TRANCOUNT=0 BEGIN TRANSACTION
3309GO
3310SET ANSI_NULLS ON
3311GO
3312SET QUOTED_IDENTIFIER ON
3313GO
3314if not exists (select * from sys.objects where [name] = N'PKSTREET_XREF' and [type] = 'PK')
3315ALTER TABLE [dbo].[STREET_CROSS_REFERENCE] ADD
3316 CONSTRAINT [PKSTREET_XREF] PRIMARY KEY CLUSTERED
3317 (
3318 [USRN],[XUSRN]
3319 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3320GO
3321
3322IF @@ERROR<>0
3323Begin
3324 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3325 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.STREET_CROSS_REFERENCE.PKSTREET_XREF')
3326END
3327GO
3328IF @@TRANCOUNT=0 BEGIN TRANSACTION
3329GO
3330SET ANSI_NULLS ON
3331GO
3332SET QUOTED_IDENTIFIER ON
3333GO
3334if not exists (select * from sys.objects where [name] = N'PK_REINSTDS_CNS' and [type] = 'PK')
3335ALTER TABLE [dbo].[REINSTDS] ADD
3336 CONSTRAINT [PK_REINSTDS_CNS] PRIMARY KEY CLUSTERED
3337 (
3338 [APGRECNO]
3339 ) ON [PRIMARY];
3340GO
3341
3342IF @@ERROR<>0
3343Begin
3344 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3345 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.REINSTDS.PK_REINSTDS_CNS')
3346END
3347GO
3348IF @@TRANCOUNT=0 BEGIN TRANSACTION
3349GO
3350SET ANSI_NULLS ON
3351GO
3352SET QUOTED_IDENTIFIER ON
3353GO
3354if not exists (select * from sys.objects where [name] = N'PK_SPCLDSG_CNS' and [type] = 'PK')
3355ALTER TABLE [dbo].[SPCLDSG] ADD
3356 CONSTRAINT [PK_SPCLDSG_CNS] PRIMARY KEY CLUSTERED
3357 (
3358 [APGRECNO]
3359 ) ON [PRIMARY];
3360GO
3361
3362IF @@ERROR<>0
3363Begin
3364 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3365 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.SPCLDSG.PK_SPCLDSG_CNS')
3366END
3367GO
3368IF @@TRANCOUNT=0 BEGIN TRANSACTION
3369GO
3370SET ANSI_NULLS ON
3371GO
3372SET QUOTED_IDENTIFIER ON
3373GO
3374if not exists (select * from sys.objects where [name] = N'PK_SPCLDESG_HISTORY' and [type] = 'PK')
3375ALTER TABLE [dbo].[SPECIAL_DESIG_HISTORY] ADD
3376 CONSTRAINT [PK_SPCLDESG_HISTORY] PRIMARY KEY CLUSTERED
3377 (
3378 [USRN],[STREET_SPECIAL_DESIG_NUM],[STREET_SPECIAL_DESIG_CODE],[SPCL_VERSION_NO]
3379 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3380GO
3381
3382IF @@ERROR<>0
3383Begin
3384 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3385 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.SPECIAL_DESIG_HISTORY.PK_SPCLDESG_HISTORY')
3386END
3387GO
3388IF @@TRANCOUNT=0 BEGIN TRANSACTION
3389GO
3390SET ANSI_NULLS ON
3391GO
3392SET QUOTED_IDENTIFIER ON
3393GO
3394if not exists (select * from sys.objects where [name] = N'PKAPP_SOURCE_NEW' and [type] = 'PK')
3395ALTER TABLE [dbo].[APPLICATION_SOURCE] ADD
3396 CONSTRAINT [PKAPP_SOURCE_NEW] PRIMARY KEY CLUSTERED
3397 (
3398 [SOURCE]
3399 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3400GO
3401
3402IF @@ERROR<>0
3403Begin
3404 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3405 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.APPLICATION_SOURCE.PKAPP_SOURCE_NEW')
3406END
3407GO
3408IF @@TRANCOUNT=0 BEGIN TRANSACTION
3409GO
3410SET ANSI_NULLS ON
3411GO
3412SET QUOTED_IDENTIFIER ON
3413GO
3414if not exists (select * from sys.objects where [name] = N'PK_AUTHORITY' and [type] = 'PK')
3415ALTER TABLE [dbo].[AUTHORITY] ADD
3416 CONSTRAINT [PK_AUTHORITY] PRIMARY KEY CLUSTERED
3417 (
3418 [AUTH_CODE]
3419 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3420GO
3421
3422IF @@ERROR<>0
3423Begin
3424 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3425 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.AUTHORITY.PK_AUTHORITY')
3426END
3427GO
3428IF @@TRANCOUNT=0 BEGIN TRANSACTION
3429GO
3430SET ANSI_NULLS ON
3431GO
3432SET QUOTED_IDENTIFIER ON
3433GO
3434if not exists (select * from sys.objects where [name] = N'PK_ASD_HISTORY' and [type] = 'PK')
3435ALTER TABLE [dbo].[ASD_HISTORY] ADD
3436 CONSTRAINT [PK_ASD_HISTORY] PRIMARY KEY CLUSTERED
3437 (
3438 [USRN],[ADDITIONAL_STREET_SEQ_NUMBER],[ASD_VERSION_NO],[AUTH_CODE]
3439 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3440GO
3441
3442IF @@ERROR<>0
3443Begin
3444 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3445 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ASD_HISTORY.PK_ASD_HISTORY')
3446END
3447GO
3448IF @@TRANCOUNT=0 BEGIN TRANSACTION
3449GO
3450SET ANSI_NULLS ON
3451GO
3452SET QUOTED_IDENTIFIER ON
3453GO
3454if not exists (select * from sys.objects where [name] = N'PK_BLPU_HISTORY' and [type] = 'PK')
3455ALTER TABLE [dbo].[BLPU_HISTORY] ADD
3456 CONSTRAINT [PK_BLPU_HISTORY] PRIMARY KEY CLUSTERED
3457 (
3458 [UPRN],[VERSION_NO]
3459 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3460GO
3461
3462IF @@ERROR<>0
3463Begin
3464 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3465 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU_HISTORY.PK_BLPU_HISTORY')
3466END
3467GO
3468IF @@TRANCOUNT=0 BEGIN TRANSACTION
3469GO
3470SET ANSI_NULLS ON
3471GO
3472SET QUOTED_IDENTIFIER ON
3473GO
3474if not exists (select * from sys.objects where [name] = N'PK_BLPU_CANDIDATE_ARCHIVE' and [type] = 'PK')
3475ALTER TABLE [dbo].[BLPU_CANDIDATE_ARCHIVE] ADD
3476 CONSTRAINT [PK_BLPU_CANDIDATE_ARCHIVE] PRIMARY KEY CLUSTERED
3477 (
3478 [BLPU_CANDIDATE_ID]
3479 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3480GO
3481
3482IF @@ERROR<>0
3483Begin
3484 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3485 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU_CANDIDATE_ARCHIVE.PK_BLPU_CANDIDATE_ARCHIVE')
3486END
3487GO
3488IF @@TRANCOUNT=0 BEGIN TRANSACTION
3489GO
3490SET ANSI_NULLS ON
3491GO
3492SET QUOTED_IDENTIFIER ON
3493GO
3494if not exists (select * from sys.objects where [name] = N'PK_CANDIDATE_TFI_ARCHIVE' and [type] = 'PK')
3495ALTER TABLE [dbo].[Candidate_TFI_ARCHIVE] ADD
3496 CONSTRAINT [PK_CANDIDATE_TFI_ARCHIVE] PRIMARY KEY CLUSTERED
3497 (
3498 [TFI_ARCHIVE_Unique]
3499 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3500GO
3501
3502IF @@ERROR<>0
3503Begin
3504 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3505 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.Candidate_TFI_ARCHIVE.PK_CANDIDATE_TFI_ARCHIVE')
3506END
3507GO
3508IF @@TRANCOUNT=0 BEGIN TRANSACTION
3509GO
3510SET ANSI_NULLS ON
3511GO
3512SET QUOTED_IDENTIFIER ON
3513GO
3514if not exists (select * from sys.objects where [name] = N'PKAPP_CROSSREF_NEW' and [type] = 'PK')
3515ALTER TABLE [dbo].[APPLICATION_CROSS_REFERENCE] ADD
3516 CONSTRAINT [PKAPP_CROSSREF_NEW] PRIMARY KEY CLUSTERED
3517 (
3518 [CROSS_REF],[SOURCE],[UPRN]
3519 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3520GO
3521
3522IF @@ERROR<>0
3523Begin
3524 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3525 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.APPLICATION_CROSS_REFERENCE.PKAPP_CROSSREF_NEW')
3526END
3527GO
3528IF @@TRANCOUNT=0 BEGIN TRANSACTION
3529GO
3530SET ANSI_NULLS ON
3531GO
3532SET QUOTED_IDENTIFIER ON
3533GO
3534if not exists (select * from sys.objects where [name] = N'PK_CANDIDATE_TFI' and [type] = 'PK')
3535ALTER TABLE [dbo].[Candidate_TFI] ADD
3536 CONSTRAINT [PK_CANDIDATE_TFI] PRIMARY KEY CLUSTERED
3537 (
3538 [TFI_Unique]
3539 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3540GO
3541
3542IF @@ERROR<>0
3543Begin
3544 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3545 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.Candidate_TFI.PK_CANDIDATE_TFI')
3546END
3547GO
3548IF @@TRANCOUNT=0 BEGIN TRANSACTION
3549GO
3550SET ANSI_NULLS ON
3551GO
3552SET QUOTED_IDENTIFIER ON
3553GO
3554if not exists (select * from sys.objects where [name] = N'pkBLPU_Class' and [type] = 'PK')
3555ALTER TABLE [dbo].[BLPU_CLASS] ADD
3556 CONSTRAINT [pkBLPU_Class] PRIMARY KEY CLUSTERED
3557 (
3558 [BLPU_ID]
3559 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3560GO
3561
3562IF @@ERROR<>0
3563Begin
3564 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3565 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU_CLASS.pkBLPU_Class')
3566END
3567GO
3568IF @@TRANCOUNT=0 BEGIN TRANSACTION
3569GO
3570SET ANSI_NULLS ON
3571GO
3572SET QUOTED_IDENTIFIER ON
3573GO
3574if not exists (select * from sys.objects where [name] = N'PKBLPU_XREF' and [type] = 'PK')
3575ALTER TABLE [dbo].[BLPU_CROSS_REFERENCE] ADD
3576 CONSTRAINT [PKBLPU_XREF] PRIMARY KEY CLUSTERED
3577 (
3578 [UPRN],[XUPRN]
3579 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3580GO
3581
3582IF @@ERROR<>0
3583Begin
3584 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3585 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU_CROSS_REFERENCE.PKBLPU_XREF')
3586END
3587GO
3588IF @@TRANCOUNT=0 BEGIN TRANSACTION
3589GO
3590SET ANSI_NULLS ON
3591GO
3592SET QUOTED_IDENTIFIER ON
3593GO
3594if not exists (select * from sys.objects where [name] = N'PK_BLPU_EXTENT' and [type] = 'PK')
3595ALTER TABLE [dbo].[BLPU_EXTENT] ADD
3596 CONSTRAINT [PK_BLPU_EXTENT] PRIMARY KEY CLUSTERED
3597 (
3598 [UPRN],[PROV_CODE]
3599 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3600GO
3601
3602IF @@ERROR<>0
3603Begin
3604 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3605 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU_EXTENT.PK_BLPU_EXTENT')
3606END
3607GO
3608IF @@TRANCOUNT=0 BEGIN TRANSACTION
3609GO
3610SET ANSI_NULLS ON
3611GO
3612SET QUOTED_IDENTIFIER ON
3613GO
3614if not exists (select * from sys.objects where [name] = N'PK_BLPU_DELETED' and [type] = 'PK')
3615ALTER TABLE [dbo].[BLPU_DELETED] ADD
3616 CONSTRAINT [PK_BLPU_DELETED] PRIMARY KEY CLUSTERED
3617 (
3618 [UPRN]
3619 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3620GO
3621
3622IF @@ERROR<>0
3623Begin
3624 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3625 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU_DELETED.PK_BLPU_DELETED')
3626END
3627GO
3628IF @@TRANCOUNT=0 BEGIN TRANSACTION
3629GO
3630SET ANSI_NULLS ON
3631GO
3632SET QUOTED_IDENTIFIER ON
3633GO
3634if not exists (select * from sys.objects where [name] = N'PKASDLOOKUP' and [type] = 'PK')
3635ALTER TABLE [dbo].[ASD_LOOKUP] ADD
3636 CONSTRAINT [PKASDLOOKUP] PRIMARY KEY CLUSTERED
3637 (
3638 [LOOKUPID],[LOOKUPTYPE]
3639 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3640GO
3641
3642IF @@ERROR<>0
3643Begin
3644 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3645 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ASD_LOOKUP.PKASDLOOKUP')
3646END
3647GO
3648IF @@TRANCOUNT=0 BEGIN TRANSACTION
3649GO
3650SET ANSI_NULLS ON
3651GO
3652SET QUOTED_IDENTIFIER ON
3653GO
3654if not exists (select * from sys.objects where [name] = N'PKBLPU_EXTENT_POLYGON' and [type] = 'PK')
3655ALTER TABLE [dbo].[BLPU_EXTENT_POLYGON] ADD
3656 CONSTRAINT [PKBLPU_EXTENT_POLYGON] PRIMARY KEY CLUSTERED
3657 (
3658 [PROVENANCE_CODE],[UPRN],[POLYGON_NUMBER]
3659 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3660GO
3661
3662IF @@ERROR<>0
3663Begin
3664 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3665 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU_EXTENT_POLYGON.PKBLPU_EXTENT_POLYGON')
3666END
3667GO
3668IF @@TRANCOUNT=0 BEGIN TRANSACTION
3669GO
3670SET ANSI_NULLS ON
3671GO
3672SET QUOTED_IDENTIFIER ON
3673GO
3674if not exists (select * from sys.objects where [name] = N'PKBLPU_EXT_POLY_VERTEX' and [type] = 'PK')
3675ALTER TABLE [dbo].[BLPU_EXTENT_POLYGON_VERTEX] ADD
3676 CONSTRAINT [PKBLPU_EXT_POLY_VERTEX] PRIMARY KEY CLUSTERED
3677 (
3678 [PROVENANCE_CODE],[UPRN],[POLYGON_NUMBER],[VERTEX_ORDER]
3679 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3680GO
3681
3682IF @@ERROR<>0
3683Begin
3684 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3685 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU_EXTENT_POLYGON_VERTEX.PKBLPU_EXT_POLY_VERTEX')
3686END
3687GO
3688IF @@TRANCOUNT=0 BEGIN TRANSACTION
3689GO
3690SET ANSI_NULLS ON
3691GO
3692SET QUOTED_IDENTIFIER ON
3693GO
3694if not exists (select * from sys.objects where [name] = N'PK_BLPU_CLASS_DELETED' and [type] = 'PK')
3695ALTER TABLE [dbo].[BLPU_CLASSIFICATION_DELETED] ADD
3696 CONSTRAINT [PK_BLPU_CLASS_DELETED] PRIMARY KEY CLUSTERED
3697 (
3698 [UNIQUE_BLPUCLASS]
3699 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3700GO
3701
3702IF @@ERROR<>0
3703Begin
3704 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3705 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU_CLASSIFICATION_DELETED.PK_BLPU_CLASS_DELETED')
3706END
3707GO
3708IF @@TRANCOUNT=0 BEGIN TRANSACTION
3709GO
3710SET ANSI_NULLS ON
3711GO
3712SET QUOTED_IDENTIFIER ON
3713GO
3714if not exists (select * from sys.objects where [name] = N'PKASDODD' and [type] = 'PK')
3715ALTER TABLE [dbo].[ASD_ODD] ADD
3716 CONSTRAINT [PKASDODD] PRIMARY KEY CLUSTERED
3717 (
3718 [ORG_ID],[DISTRICT_ID]
3719 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3720GO
3721
3722IF @@ERROR<>0
3723Begin
3724 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3725 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ASD_ODD.PKASDODD')
3726END
3727GO
3728IF @@TRANCOUNT=0 BEGIN TRANSACTION
3729GO
3730SET ANSI_NULLS ON
3731GO
3732SET QUOTED_IDENTIFIER ON
3733GO
3734if not exists (select * from sys.objects where [name] = N'PK_ADTNLSTR_CNS' and [type] = 'PK')
3735ALTER TABLE [dbo].[ADTNLSTR] ADD
3736 CONSTRAINT [PK_ADTNLSTR_CNS] PRIMARY KEY CLUSTERED
3737 (
3738 [APGRECNO]
3739 ) ON [PRIMARY];
3740GO
3741
3742IF @@ERROR<>0
3743Begin
3744 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3745 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ADTNLSTR.PK_ADTNLSTR_CNS')
3746END
3747GO
3748IF @@TRANCOUNT=0 BEGIN TRANSACTION
3749GO
3750SET ANSI_NULLS ON
3751GO
3752SET QUOTED_IDENTIFIER ON
3753GO
3754if not exists (select * from sys.objects where [name] = N'PKESUXREF' and [type] = 'PK')
3755ALTER TABLE [dbo].[ESU_CROSS_REFERENCE] ADD
3756 CONSTRAINT [PKESUXREF] PRIMARY KEY CLUSTERED
3757 (
3758 [ESU_ID],[USRN],[XREF_VERSION_NUMBER],[USRN_VERSION_NUMBER]
3759 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3760GO
3761
3762IF @@ERROR<>0
3763Begin
3764 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3765 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ESU_CROSS_REFERENCE.PKESUXREF')
3766END
3767GO
3768IF @@TRANCOUNT=0 BEGIN TRANSACTION
3769GO
3770SET ANSI_NULLS ON
3771GO
3772SET QUOTED_IDENTIFIER ON
3773GO
3774if not exists (select * from sys.objects where [name] = N'PK_ALIASINFO' and [type] = 'PK')
3775ALTER TABLE [dbo].[ALIASINFO] ADD
3776 CONSTRAINT [PK_ALIASINFO] PRIMARY KEY CLUSTERED
3777 (
3778 [ALIAS_ID]
3779 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3780GO
3781
3782IF @@ERROR<>0
3783Begin
3784 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3785 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ALIASINFO.PK_ALIASINFO')
3786END
3787GO
3788IF @@TRANCOUNT=0 BEGIN TRANSACTION
3789GO
3790SET ANSI_NULLS ON
3791GO
3792SET QUOTED_IDENTIFIER ON
3793GO
3794if not exists (select * from sys.objects where [name] = N'PK_BLPU_CLASSIFICATION' and [type] = 'PK')
3795ALTER TABLE [dbo].[BLPU_CLASSIFICATION] ADD
3796 CONSTRAINT [PK_BLPU_CLASSIFICATION] PRIMARY KEY CLUSTERED
3797 (
3798 [UNIQUE_BLPUCLASS]
3799 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3800GO
3801
3802IF @@ERROR<>0
3803Begin
3804 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3805 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU_CLASSIFICATION.PK_BLPU_CLASSIFICATION')
3806END
3807GO
3808IF @@TRANCOUNT=0 BEGIN TRANSACTION
3809GO
3810SET ANSI_NULLS ON
3811GO
3812SET QUOTED_IDENTIFIER ON
3813GO
3814if not exists (select * from sys.objects where [name] = N'PK_ADDITIONAL_STREET' and [type] = 'PK')
3815ALTER TABLE [dbo].[ADDITIONAL_STREET] ADD
3816 CONSTRAINT [PK_ADDITIONAL_STREET] PRIMARY KEY CLUSTERED
3817 (
3818 [USRN],[ADDITIONAL_STREET_SEQ_NUMBER],[AUTH_CODE]
3819 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3820GO
3821
3822IF @@ERROR<>0
3823Begin
3824 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3825 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ADDITIONAL_STREET.PK_ADDITIONAL_STREET')
3826END
3827GO
3828IF @@TRANCOUNT=0 BEGIN TRANSACTION
3829GO
3830SET ANSI_NULLS ON
3831GO
3832SET QUOTED_IDENTIFIER ON
3833GO
3834if not exists (select * from sys.objects where [name] = N'PKESUXREFHISTORY' and [type] = 'PK')
3835ALTER TABLE [dbo].[ESU_XREF_HISTORY] ADD
3836 CONSTRAINT [PKESUXREFHISTORY] PRIMARY KEY CLUSTERED
3837 (
3838 [ESU_ID],[USRN],[XREF_VERSION_NUMBER],[USRN_VERSION_NUMBER]
3839 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3840GO
3841
3842IF @@ERROR<>0
3843Begin
3844 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3845 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ESU_XREF_HISTORY.PKESUXREFHISTORY')
3846END
3847GO
3848IF @@TRANCOUNT=0 BEGIN TRANSACTION
3849GO
3850SET ANSI_NULLS ON
3851GO
3852SET QUOTED_IDENTIFIER ON
3853GO
3854if not exists (select * from sys.objects where [name] = N'PKADMIN_AREANAME' and [type] = 'PK')
3855ALTER TABLE [dbo].[ADMIN_AREA_NAME] ADD
3856 CONSTRAINT [PKADMIN_AREANAME] PRIMARY KEY CLUSTERED
3857 (
3858 [AAN_REF]
3859 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3860GO
3861
3862IF @@ERROR<>0
3863Begin
3864 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3865 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ADMIN_AREA_NAME.PKADMIN_AREANAME')
3866END
3867GO
3868IF @@TRANCOUNT=0 BEGIN TRANSACTION
3869GO
3870SET ANSI_NULLS ON
3871GO
3872SET QUOTED_IDENTIFIER ON
3873GO
3874if not exists (select * from sys.objects where [name] = N'PKESUHISTORY' and [type] = 'PK')
3875ALTER TABLE [dbo].[ESU_HISTORY] ADD
3876 CONSTRAINT [PKESUHISTORY] PRIMARY KEY CLUSTERED
3877 (
3878 [ESU_ID],[VERSION_NO]
3879 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3880GO
3881
3882IF @@ERROR<>0
3883Begin
3884 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3885 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ESU_HISTORY.PKESUHISTORY')
3886END
3887GO
3888IF @@TRANCOUNT=0 BEGIN TRANSACTION
3889GO
3890SET ANSI_NULLS ON
3891GO
3892SET QUOTED_IDENTIFIER ON
3893GO
3894if not exists (select * from sys.objects where [name] = N'PK_BLPU' and [type] = 'PK')
3895ALTER TABLE [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] ADD
3896 CONSTRAINT [PK_BLPU] PRIMARY KEY CLUSTERED
3897 (
3898 [UPRN]
3899 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3900GO
3901
3902IF @@ERROR<>0
3903Begin
3904 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3905 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BASIC_LAND_AND_PROPERTY_UNIT.PK_BLPU')
3906END
3907GO
3908IF @@TRANCOUNT=0 BEGIN TRANSACTION
3909GO
3910SET ANSI_NULLS ON
3911GO
3912SET QUOTED_IDENTIFIER ON
3913GO
3914if not exists (select * from sys.objects where [name] = N'PK_ESU' and [type] = 'PK')
3915ALTER TABLE [dbo].[ELEMENTARY_STREET_UNIT] ADD
3916 CONSTRAINT [PK_ESU] PRIMARY KEY CLUSTERED
3917 (
3918 [ESU_ID]
3919 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3920GO
3921
3922IF @@ERROR<>0
3923Begin
3924 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3925 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ELEMENTARY_STREET_UNIT.PK_ESU')
3926END
3927GO
3928IF @@TRANCOUNT=0 BEGIN TRANSACTION
3929GO
3930SET ANSI_NULLS ON
3931GO
3932SET QUOTED_IDENTIFIER ON
3933GO
3934if not exists (select * from sys.objects where [name] = N'PK_ESU_CNS' and [type] = 'PK')
3935ALTER TABLE [dbo].[ESU] ADD
3936 CONSTRAINT [PK_ESU_CNS] PRIMARY KEY CLUSTERED
3937 (
3938 [APGRECNO]
3939 ) ON [PRIMARY];
3940GO
3941
3942IF @@ERROR<>0
3943Begin
3944 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3945 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ESU.PK_ESU_CNS')
3946END
3947GO
3948IF @@TRANCOUNT=0 BEGIN TRANSACTION
3949GO
3950SET ANSI_NULLS ON
3951GO
3952SET QUOTED_IDENTIFIER ON
3953GO
3954if not exists (select * from sys.objects where [name] = N'PK_BLPU_CANDIDATE' and [type] = 'PK')
3955ALTER TABLE [dbo].[BLPU_CANDIDATE] ADD
3956 CONSTRAINT [PK_BLPU_CANDIDATE] PRIMARY KEY CLUSTERED
3957 (
3958 [BLPU_CANDIDATE_ID]
3959 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3960GO
3961
3962IF @@ERROR<>0
3963Begin
3964 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3965 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU_CANDIDATE.PK_BLPU_CANDIDATE')
3966END
3967GO
3968IF @@TRANCOUNT=0 BEGIN TRANSACTION
3969GO
3970SET ANSI_NULLS ON
3971GO
3972SET QUOTED_IDENTIFIER ON
3973GO
3974if not exists (select * from sys.objects where [name] = N'PKESUCOORDHISTORY' and [type] = 'PK')
3975ALTER TABLE [dbo].[ESU_COORD_HISTORY] ADD
3976 CONSTRAINT [PKESUCOORDHISTORY] PRIMARY KEY CLUSTERED
3977 (
3978 [ESU_ID],[VERSION_NO],[NUM_COORD]
3979 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
3980GO
3981
3982IF @@ERROR<>0
3983Begin
3984 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
3985 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ESU_COORD_HISTORY.PKESUCOORDHISTORY')
3986END
3987GO
3988IF @@TRANCOUNT=0 BEGIN TRANSACTION
3989GO
3990SET ANSI_NULLS ON
3991GO
3992SET QUOTED_IDENTIFIER ON
3993GO
3994if not exists (select * from sys.objects where [name] = N'pkAltLPI' and [type] = 'PK')
3995ALTER TABLE [dbo].[ALT_STREET_LPI] ADD
3996 CONSTRAINT [pkAltLPI] PRIMARY KEY CLUSTERED
3997 (
3998 [ALT_LPI]
3999 ) WITH (FILLFACTOR = 90) ON [PRIMARY];
4000GO
4001
4002IF @@ERROR<>0
4003Begin
4004 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4005 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.ALT_STREET_LPI.pkAltLPI')
4006END
4007GO
4008IF @@TRANCOUNT=0 BEGIN TRANSACTION
4009GO
4010SET ANSI_NULLS ON
4011GO
4012SET QUOTED_IDENTIFIER ON
4013GO
4014if not exists (select * from sys.objects where [name] = N'PK_BLPU_CNS' and [type] = 'PK')
4015ALTER TABLE [dbo].[BLPU] ADD
4016 CONSTRAINT [PK_BLPU_CNS] PRIMARY KEY CLUSTERED
4017 (
4018 [APGRECNO]
4019 ) ON [PRIMARY];
4020GO
4021
4022IF @@ERROR<>0
4023Begin
4024 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4025 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add primary key dbo.BLPU.PK_BLPU_CNS')
4026END
4027GO
4028IF @@TRANCOUNT=0 BEGIN TRANSACTION
4029GO
4030if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_CLASS_BLPUCLS' and [object_id] = object_id(N'[dbo].[BLPU_CLASS]'))
4031CREATE INDEX [IDX_BLPU_CLASS_BLPUCLS] ON [dbo].[BLPU_CLASS] ([BLPU_Class]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4032GO
4033IF @@ERROR<>0
4034Begin
4035 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4036 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU_CLASS.IDX_BLPU_CLASS_BLPUCLS')
4037END
4038GO
4039
4040IF @@TRANCOUNT=0 BEGIN TRANSACTION
4041GO
4042if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_CAN_KEY' and [object_id] = object_id(N'[dbo].[BLPU]'))
4043CREATE INDEX [IDX_BLPU_CAN_KEY] ON [dbo].[BLPU] ([CAN_KEY]) ON [PRIMARY];
4044GO
4045IF @@ERROR<>0
4046Begin
4047 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4048 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU.IDX_BLPU_CAN_KEY')
4049END
4050GO
4051
4052IF @@TRANCOUNT=0 BEGIN TRANSACTION
4053GO
4054if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_DISPLAYFLG' and [object_id] = object_id(N'[dbo].[BLPU]'))
4055CREATE INDEX [IDX_BLPU_DISPLAYFLG] ON [dbo].[BLPU] ([DISPLAYFLG]) ON [PRIMARY];
4056GO
4057IF @@ERROR<>0
4058Begin
4059 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4060 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU.IDX_BLPU_DISPLAYFLG')
4061END
4062GO
4063
4064IF @@TRANCOUNT=0 BEGIN TRANSACTION
4065GO
4066if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_LOG_STATUS' and [object_id] = object_id(N'[dbo].[BLPU]'))
4067CREATE INDEX [IDX_BLPU_LOG_STATUS] ON [dbo].[BLPU] ([LOG_STATUS]) ON [PRIMARY];
4068GO
4069IF @@ERROR<>0
4070Begin
4071 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4072 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU.IDX_BLPU_LOG_STATUS')
4073END
4074GO
4075
4076IF @@TRANCOUNT=0 BEGIN TRANSACTION
4077GO
4078if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_POSTCODE' and [object_id] = object_id(N'[dbo].[BLPU]'))
4079CREATE INDEX [IDX_BLPU_POSTCODE] ON [dbo].[BLPU] ([POSTCODE]) ON [PRIMARY];
4080GO
4081IF @@ERROR<>0
4082Begin
4083 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4084 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU.IDX_BLPU_POSTCODE')
4085END
4086GO
4087
4088IF @@TRANCOUNT=0 BEGIN TRANSACTION
4089GO
4090if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_PROP_TYPE' and [object_id] = object_id(N'[dbo].[BLPU]'))
4091CREATE INDEX [IDX_BLPU_PROP_TYPE] ON [dbo].[BLPU] ([PROP_TYPE]) ON [PRIMARY];
4092GO
4093IF @@ERROR<>0
4094Begin
4095 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4096 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU.IDX_BLPU_PROP_TYPE')
4097END
4098GO
4099
4100IF @@TRANCOUNT=0 BEGIN TRANSACTION
4101GO
4102if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_PROV_CODE' and [object_id] = object_id(N'[dbo].[BLPU]'))
4103CREATE INDEX [IDX_BLPU_PROV_CODE] ON [dbo].[BLPU] ([PROV_CODE]) ON [PRIMARY];
4104GO
4105IF @@ERROR<>0
4106Begin
4107 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4108 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU.IDX_BLPU_PROV_CODE')
4109END
4110GO
4111
4112IF @@TRANCOUNT=0 BEGIN TRANSACTION
4113GO
4114if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_UPDDATE' and [object_id] = object_id(N'[dbo].[BLPU]'))
4115CREATE INDEX [IDX_BLPU_UPDDATE] ON [dbo].[BLPU] ([UPDDATE]) ON [PRIMARY];
4116GO
4117IF @@ERROR<>0
4118Begin
4119 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4120 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU.IDX_BLPU_UPDDATE')
4121END
4122GO
4123
4124IF @@TRANCOUNT=0 BEGIN TRANSACTION
4125GO
4126if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_UPRN' and [object_id] = object_id(N'[dbo].[BLPU]'))
4127CREATE INDEX [IDX_BLPU_UPRN] ON [dbo].[BLPU] ([UPRN]) ON [PRIMARY];
4128GO
4129IF @@ERROR<>0
4130Begin
4131 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4132 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU.IDX_BLPU_UPRN')
4133END
4134GO
4135
4136IF @@TRANCOUNT=0 BEGIN TRANSACTION
4137GO
4138if not exists (select * from sys.indexes where [name] = N'IDX_STREET_DESC_LASTUPDATE' and [object_id] = object_id(N'[dbo].[STREET_DESCRIPTOR]'))
4139CREATE INDEX [IDX_STREET_DESC_LASTUPDATE] ON [dbo].[STREET_DESCRIPTOR] ([LAST_UPDATE_DATE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4140GO
4141IF @@ERROR<>0
4142Begin
4143 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4144 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.STREET_DESCRIPTOR.IDX_STREET_DESC_LASTUPDATE')
4145END
4146GO
4147
4148IF @@TRANCOUNT=0 BEGIN TRANSACTION
4149GO
4150if not exists (select * from sys.indexes where [name] = N'IDX_STREET_LASTUPDATE' and [object_id] = object_id(N'[dbo].[STREET]'))
4151CREATE INDEX [IDX_STREET_LASTUPDATE] ON [dbo].[STREET] ([LAST_UPDATE_DATE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4152GO
4153IF @@ERROR<>0
4154Begin
4155 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4156 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.STREET.IDX_STREET_LASTUPDATE')
4157END
4158GO
4159
4160IF @@TRANCOUNT=0 BEGIN TRANSACTION
4161GO
4162if not exists (select * from sys.indexes where [name] = N'idx_STREET_HISTORY_USRN' and [object_id] = object_id(N'[dbo].[STREET_HISTORY]'))
4163CREATE INDEX [idx_STREET_HISTORY_USRN] ON [dbo].[STREET_HISTORY] ([USRN]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4164GO
4165IF @@ERROR<>0
4166Begin
4167 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4168 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.STREET_HISTORY.idx_STREET_HISTORY_USRN')
4169END
4170GO
4171
4172IF @@TRANCOUNT=0 BEGIN TRANSACTION
4173GO
4174if not exists (select * from sys.indexes where [name] = N'idx_STREET_HISTORY_VERSION_NO' and [object_id] = object_id(N'[dbo].[STREET_HISTORY]'))
4175CREATE INDEX [idx_STREET_HISTORY_VERSION_NO] ON [dbo].[STREET_HISTORY] ([VERSION_NO]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4176GO
4177IF @@ERROR<>0
4178Begin
4179 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4180 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.STREET_HISTORY.idx_STREET_HISTORY_VERSION_NO')
4181END
4182GO
4183
4184IF @@TRANCOUNT=0 BEGIN TRANSACTION
4185GO
4186if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_CUSTODIANCODE' and [object_id] = object_id(N'[dbo].[BASIC_LAND_AND_PROPERTY_UNIT]'))
4187CREATE INDEX [IDX_BLPU_CUSTODIANCODE] ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] ([CUSTODIAN_CODE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4188GO
4189IF @@ERROR<>0
4190Begin
4191 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4192 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BASIC_LAND_AND_PROPERTY_UNIT.IDX_BLPU_CUSTODIANCODE')
4193END
4194GO
4195
4196IF @@TRANCOUNT=0 BEGIN TRANSACTION
4197GO
4198if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_LASTUPDATE' and [object_id] = object_id(N'[dbo].[BASIC_LAND_AND_PROPERTY_UNIT]'))
4199CREATE INDEX [IDX_BLPU_LASTUPDATE] ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] ([LAST_UPDATE_DATE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4200GO
4201IF @@ERROR<>0
4202Begin
4203 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4204 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BASIC_LAND_AND_PROPERTY_UNIT.IDX_BLPU_LASTUPDATE')
4205END
4206GO
4207
4208IF @@TRANCOUNT=0 BEGIN TRANSACTION
4209GO
4210if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_LOCALONLY_PRIORITY' and [object_id] = object_id(N'[dbo].[BASIC_LAND_AND_PROPERTY_UNIT]'))
4211CREATE INDEX [IDX_BLPU_LOCALONLY_PRIORITY] ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] ([LOCALONLY_PRIORITY]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4212GO
4213IF @@ERROR<>0
4214Begin
4215 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4216 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BASIC_LAND_AND_PROPERTY_UNIT.IDX_BLPU_LOCALONLY_PRIORITY')
4217END
4218GO
4219
4220IF @@TRANCOUNT=0 BEGIN TRANSACTION
4221GO
4222if not exists (select * from sys.indexes where [name] = N'Idx_BLPU_LOGICALSTATUS' and [object_id] = object_id(N'[dbo].[BASIC_LAND_AND_PROPERTY_UNIT]'))
4223CREATE INDEX [Idx_BLPU_LOGICALSTATUS] ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] ([LOGICAL_STATUS]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4224GO
4225IF @@ERROR<>0
4226Begin
4227 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4228 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BASIC_LAND_AND_PROPERTY_UNIT.Idx_BLPU_LOGICALSTATUS')
4229END
4230GO
4231
4232IF @@TRANCOUNT=0 BEGIN TRANSACTION
4233GO
4234if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_org' and [object_id] = object_id(N'[dbo].[BASIC_LAND_AND_PROPERTY_UNIT]'))
4235CREATE INDEX [IDX_BLPU_org] ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] ([ORGANISATION]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4236GO
4237IF @@ERROR<>0
4238Begin
4239 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4240 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BASIC_LAND_AND_PROPERTY_UNIT.IDX_BLPU_org')
4241END
4242GO
4243
4244IF @@TRANCOUNT=0 BEGIN TRANSACTION
4245GO
4246if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_PARENT_UPRN' and [object_id] = object_id(N'[dbo].[BASIC_LAND_AND_PROPERTY_UNIT]'))
4247CREATE INDEX [IDX_BLPU_PARENT_UPRN] ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] ([PARENT_UPRN]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4248GO
4249IF @@ERROR<>0
4250Begin
4251 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4252 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BASIC_LAND_AND_PROPERTY_UNIT.IDX_BLPU_PARENT_UPRN')
4253END
4254GO
4255
4256IF @@TRANCOUNT=0 BEGIN TRANSACTION
4257GO
4258if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_PARISH' and [object_id] = object_id(N'[dbo].[BASIC_LAND_AND_PROPERTY_UNIT]'))
4259CREATE INDEX [IDX_BLPU_PARISH] ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] ([PARISHCODE]) ON [PRIMARY];
4260GO
4261IF @@ERROR<>0
4262Begin
4263 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4264 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BASIC_LAND_AND_PROPERTY_UNIT.IDX_BLPU_PARISH')
4265END
4266GO
4267
4268IF @@TRANCOUNT=0 BEGIN TRANSACTION
4269GO
4270if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_WARD' and [object_id] = object_id(N'[dbo].[BASIC_LAND_AND_PROPERTY_UNIT]'))
4271CREATE INDEX [IDX_BLPU_WARD] ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] ([WARDCODE]) ON [PRIMARY];
4272GO
4273IF @@ERROR<>0
4274Begin
4275 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4276 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BASIC_LAND_AND_PROPERTY_UNIT.IDX_BLPU_WARD')
4277END
4278GO
4279
4280IF @@TRANCOUNT=0 BEGIN TRANSACTION
4281GO
4282if not exists (select * from sys.indexes where [name] = N'IndxBLPULOGSTATUS' and [object_id] = object_id(N'[dbo].[BASIC_LAND_AND_PROPERTY_UNIT]'))
4283CREATE INDEX [IndxBLPULOGSTATUS] ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] ([LOGICAL_STATUS]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4284GO
4285IF @@ERROR<>0
4286Begin
4287 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4288 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BASIC_LAND_AND_PROPERTY_UNIT.IndxBLPULOGSTATUS')
4289END
4290GO
4291
4292IF @@TRANCOUNT=0 BEGIN TRANSACTION
4293GO
4294if not exists (select * from sys.indexes where [name] = N'indxPropertyType' and [object_id] = object_id(N'[dbo].[BASIC_LAND_AND_PROPERTY_UNIT]'))
4295CREATE INDEX [indxPropertyType] ON [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] ([PROPERTY_TYPE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4296GO
4297IF @@ERROR<>0
4298Begin
4299 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4300 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BASIC_LAND_AND_PROPERTY_UNIT.indxPropertyType')
4301END
4302GO
4303
4304IF @@TRANCOUNT=0 BEGIN TRANSACTION
4305GO
4306if not exists (select * from sys.indexes where [name] = N'IDX_NODE_DISPLAYFLG' and [object_id] = object_id(N'[dbo].[NODE]'))
4307CREATE INDEX [IDX_NODE_DISPLAYFLG] ON [dbo].[NODE] ([DISPLAYFLG]) ON [PRIMARY];
4308GO
4309IF @@ERROR<>0
4310Begin
4311 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4312 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.NODE.IDX_NODE_DISPLAYFLG')
4313END
4314GO
4315
4316IF @@TRANCOUNT=0 BEGIN TRANSACTION
4317GO
4318if not exists (select * from sys.indexes where [name] = N'IDX_NODE_UPDDATE' and [object_id] = object_id(N'[dbo].[NODE]'))
4319CREATE INDEX [IDX_NODE_UPDDATE] ON [dbo].[NODE] ([UPDDATE]) ON [PRIMARY];
4320GO
4321IF @@ERROR<>0
4322Begin
4323 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4324 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.NODE.IDX_NODE_UPDDATE')
4325END
4326GO
4327
4328IF @@TRANCOUNT=0 BEGIN TRANSACTION
4329GO
4330if not exists (select * from sys.indexes where [name] = N'UIDX_NODE_NODE_ID' and [object_id] = object_id(N'[dbo].[NODE]'))
4331CREATE UNIQUE INDEX [UIDX_NODE_NODE_ID] ON [dbo].[NODE] ([NODE_ID]) ON [PRIMARY];
4332GO
4333IF @@ERROR<>0
4334Begin
4335 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4336 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.NODE.UIDX_NODE_NODE_ID')
4337END
4338GO
4339
4340IF @@TRANCOUNT=0 BEGIN TRANSACTION
4341GO
4342if not exists (select * from sys.indexes where [name] = N'IDX_LPI_DELETED_LOGSTATUS' and [object_id] = object_id(N'[dbo].[LPI_DELETED]'))
4343CREATE INDEX [IDX_LPI_DELETED_LOGSTATUS] ON [dbo].[LPI_DELETED] ([LOGICAL_STATUS]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4344GO
4345IF @@ERROR<>0
4346Begin
4347 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4348 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LPI_DELETED.IDX_LPI_DELETED_LOGSTATUS')
4349END
4350GO
4351
4352IF @@TRANCOUNT=0 BEGIN TRANSACTION
4353GO
4354if not exists (select * from sys.indexes where [name] = N'IDX_LPI_DELETED_POSTCODE' and [object_id] = object_id(N'[dbo].[LPI_DELETED]'))
4355CREATE INDEX [IDX_LPI_DELETED_POSTCODE] ON [dbo].[LPI_DELETED] ([POSTCODE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4356GO
4357IF @@ERROR<>0
4358Begin
4359 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4360 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LPI_DELETED.IDX_LPI_DELETED_POSTCODE')
4361END
4362GO
4363
4364IF @@TRANCOUNT=0 BEGIN TRANSACTION
4365GO
4366if not exists (select * from sys.indexes where [name] = N'IDX_LPI_DELETED_UPRN' and [object_id] = object_id(N'[dbo].[LPI_DELETED]'))
4367CREATE INDEX [IDX_LPI_DELETED_UPRN] ON [dbo].[LPI_DELETED] ([UPRN]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4368GO
4369IF @@ERROR<>0
4370Begin
4371 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4372 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LPI_DELETED.IDX_LPI_DELETED_UPRN')
4373END
4374GO
4375
4376IF @@TRANCOUNT=0 BEGIN TRANSACTION
4377GO
4378if not exists (select * from sys.indexes where [name] = N'IDX_LPI_DELETED_USRN' and [object_id] = object_id(N'[dbo].[LPI_DELETED]'))
4379CREATE INDEX [IDX_LPI_DELETED_USRN] ON [dbo].[LPI_DELETED] ([USRN]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4380GO
4381IF @@ERROR<>0
4382Begin
4383 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4384 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LPI_DELETED.IDX_LPI_DELETED_USRN')
4385END
4386GO
4387
4388IF @@TRANCOUNT=0 BEGIN TRANSACTION
4389GO
4390if not exists (select * from sys.indexes where [name] = N'IDX_ESU_DISPLAYFLG' and [object_id] = object_id(N'[dbo].[ESU]'))
4391CREATE INDEX [IDX_ESU_DISPLAYFLG] ON [dbo].[ESU] ([DISPLAYFLG]) ON [PRIMARY];
4392GO
4393IF @@ERROR<>0
4394Begin
4395 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4396 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.ESU.IDX_ESU_DISPLAYFLG')
4397END
4398GO
4399
4400IF @@TRANCOUNT=0 BEGIN TRANSACTION
4401GO
4402if not exists (select * from sys.indexes where [name] = N'IDX_ESU_UPDDATE' and [object_id] = object_id(N'[dbo].[ESU]'))
4403CREATE INDEX [IDX_ESU_UPDDATE] ON [dbo].[ESU] ([UPDDATE]) ON [PRIMARY];
4404GO
4405IF @@ERROR<>0
4406Begin
4407 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4408 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.ESU.IDX_ESU_UPDDATE')
4409END
4410GO
4411
4412IF @@TRANCOUNT=0 BEGIN TRANSACTION
4413GO
4414if not exists (select * from sys.indexes where [name] = N'UIDX_ESU_ESU_ID' and [object_id] = object_id(N'[dbo].[ESU]'))
4415CREATE UNIQUE INDEX [UIDX_ESU_ESU_ID] ON [dbo].[ESU] ([ESU_ID]) ON [PRIMARY];
4416GO
4417IF @@ERROR<>0
4418Begin
4419 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4420 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.ESU.UIDX_ESU_ESU_ID')
4421END
4422GO
4423
4424IF @@TRANCOUNT=0 BEGIN TRANSACTION
4425GO
4426if not exists (select * from sys.indexes where [name] = N'IDX_STREET_SP_DISPLAYFLG' and [object_id] = object_id(N'[dbo].[STREET_SP]'))
4427CREATE INDEX [IDX_STREET_SP_DISPLAYFLG] ON [dbo].[STREET_SP] ([DISPLAYFLG]) ON [PRIMARY];
4428GO
4429IF @@ERROR<>0
4430Begin
4431 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4432 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.STREET_SP.IDX_STREET_SP_DISPLAYFLG')
4433END
4434GO
4435
4436IF @@TRANCOUNT=0 BEGIN TRANSACTION
4437GO
4438if not exists (select * from sys.indexes where [name] = N'IDX_STREET_SP_SUB_TYPE' and [object_id] = object_id(N'[dbo].[STREET_SP]'))
4439CREATE INDEX [IDX_STREET_SP_SUB_TYPE] ON [dbo].[STREET_SP] ([SUB_TYPE]) ON [PRIMARY];
4440GO
4441IF @@ERROR<>0
4442Begin
4443 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4444 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.STREET_SP.IDX_STREET_SP_SUB_TYPE')
4445END
4446GO
4447
4448IF @@TRANCOUNT=0 BEGIN TRANSACTION
4449GO
4450if not exists (select * from sys.indexes where [name] = N'IDX_STREET_SP_UPDDATE' and [object_id] = object_id(N'[dbo].[STREET_SP]'))
4451CREATE INDEX [IDX_STREET_SP_UPDDATE] ON [dbo].[STREET_SP] ([UPDDATE]) ON [PRIMARY];
4452GO
4453IF @@ERROR<>0
4454Begin
4455 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4456 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.STREET_SP.IDX_STREET_SP_UPDDATE')
4457END
4458GO
4459
4460IF @@TRANCOUNT=0 BEGIN TRANSACTION
4461GO
4462if not exists (select * from sys.indexes where [name] = N'UIDX_STREET_SP_USRN' and [object_id] = object_id(N'[dbo].[STREET_SP]'))
4463CREATE UNIQUE INDEX [UIDX_STREET_SP_USRN] ON [dbo].[STREET_SP] ([USRN]) ON [PRIMARY];
4464GO
4465IF @@ERROR<>0
4466Begin
4467 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4468 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.STREET_SP.UIDX_STREET_SP_USRN')
4469END
4470GO
4471
4472IF @@TRANCOUNT=0 BEGIN TRANSACTION
4473GO
4474if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_DELETED_CUSTCODE' and [object_id] = object_id(N'[dbo].[BLPU_DELETED]'))
4475CREATE INDEX [IDX_BLPU_DELETED_CUSTCODE] ON [dbo].[BLPU_DELETED] ([CUSTODIAN_CODE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4476GO
4477IF @@ERROR<>0
4478Begin
4479 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4480 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU_DELETED.IDX_BLPU_DELETED_CUSTCODE')
4481END
4482GO
4483
4484IF @@TRANCOUNT=0 BEGIN TRANSACTION
4485GO
4486if not exists (select * from sys.indexes where [name] = N'IDX_BLPU_DELETED_LOGSTATUS' and [object_id] = object_id(N'[dbo].[BLPU_DELETED]'))
4487CREATE INDEX [IDX_BLPU_DELETED_LOGSTATUS] ON [dbo].[BLPU_DELETED] ([LOGICAL_STATUS]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4488GO
4489IF @@ERROR<>0
4490Begin
4491 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4492 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU_DELETED.IDX_BLPU_DELETED_LOGSTATUS')
4493END
4494GO
4495
4496IF @@TRANCOUNT=0 BEGIN TRANSACTION
4497GO
4498if not exists (select * from sys.indexes where [name] = N'IDX_LPI_LANGUAGE_CODE' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4499CREATE INDEX [IDX_LPI_LANGUAGE_CODE] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([LANGUAGE_CODE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4500GO
4501IF @@ERROR<>0
4502Begin
4503 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4504 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.IDX_LPI_LANGUAGE_CODE')
4505END
4506GO
4507
4508IF @@TRANCOUNT=0 BEGIN TRANSACTION
4509GO
4510if not exists (select * from sys.indexes where [name] = N'IDX_LPI_LASTUPDATE' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4511CREATE INDEX [IDX_LPI_LASTUPDATE] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([LAST_UPDATE_DATE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4512GO
4513IF @@ERROR<>0
4514Begin
4515 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4516 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.IDX_LPI_LASTUPDATE')
4517END
4518GO
4519
4520IF @@TRANCOUNT=0 BEGIN TRANSACTION
4521GO
4522if not exists (select * from sys.indexes where [name] = N'IDX_LPI_LOCALONLY_PRIORITY' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4523CREATE INDEX [IDX_LPI_LOCALONLY_PRIORITY] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([LOCALONLY_PRIORITY]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4524GO
4525IF @@ERROR<>0
4526Begin
4527 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4528 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.IDX_LPI_LOCALONLY_PRIORITY')
4529END
4530GO
4531
4532IF @@TRANCOUNT=0 BEGIN TRANSACTION
4533GO
4534if not exists (select * from sys.indexes where [name] = N'Idx_LPI_LOGICALSTATUS' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4535CREATE INDEX [Idx_LPI_LOGICALSTATUS] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([LOGICAL_STATUS]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4536GO
4537IF @@ERROR<>0
4538Begin
4539 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4540 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.Idx_LPI_LOGICALSTATUS')
4541END
4542GO
4543
4544IF @@TRANCOUNT=0 BEGIN TRANSACTION
4545GO
4546if not exists (select * from sys.indexes where [name] = N'idx_LPI_PAON' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4547CREATE INDEX [idx_LPI_PAON] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([PAO_FROM],[PAO_FROM_SUFF],[PAO_TO],[PAO_TO_SUFF],[PAO_NAME]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4548GO
4549IF @@ERROR<>0
4550Begin
4551 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4552 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.idx_LPI_PAON')
4553END
4554GO
4555
4556IF @@TRANCOUNT=0 BEGIN TRANSACTION
4557GO
4558if not exists (select * from sys.indexes where [name] = N'IDX_LPI_POLICEBEATCODE' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4559CREATE INDEX [IDX_LPI_POLICEBEATCODE] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([POLICEBEATCODE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4560GO
4561IF @@ERROR<>0
4562Begin
4563 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4564 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.IDX_LPI_POLICEBEATCODE')
4565END
4566GO
4567
4568IF @@TRANCOUNT=0 BEGIN TRANSACTION
4569GO
4570if not exists (select * from sys.indexes where [name] = N'IDX_LPI_POLICEFORCEAREA' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4571CREATE INDEX [IDX_LPI_POLICEFORCEAREA] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([POLICEFORCEAREA]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4572GO
4573IF @@ERROR<>0
4574Begin
4575 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4576 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.IDX_LPI_POLICEFORCEAREA')
4577END
4578GO
4579
4580IF @@TRANCOUNT=0 BEGIN TRANSACTION
4581GO
4582if not exists (select * from sys.indexes where [name] = N'Idx_LPI_POSTCODE' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4583CREATE INDEX [Idx_LPI_POSTCODE] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([POSTCODE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4584GO
4585IF @@ERROR<>0
4586Begin
4587 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4588 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.Idx_LPI_POSTCODE')
4589END
4590GO
4591
4592IF @@TRANCOUNT=0 BEGIN TRANSACTION
4593GO
4594if not exists (select * from sys.indexes where [name] = N'idx_LPI_SAON' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4595CREATE INDEX [idx_LPI_SAON] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([SAO_FROM],[SAO_FROM_SUFF],[SAO_TO],[SAO_TO_SUFF],[SAO_NAME]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4596GO
4597IF @@ERROR<>0
4598Begin
4599 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4600 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.idx_LPI_SAON')
4601END
4602GO
4603
4604IF @@TRANCOUNT=0 BEGIN TRANSACTION
4605GO
4606if not exists (select * from sys.indexes where [name] = N'IDX_LPI_UPRN' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4607CREATE INDEX [IDX_LPI_UPRN] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([UPRN]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4608GO
4609IF @@ERROR<>0
4610Begin
4611 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4612 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.IDX_LPI_UPRN')
4613END
4614GO
4615
4616IF @@TRANCOUNT=0 BEGIN TRANSACTION
4617GO
4618if not exists (select * from sys.indexes where [name] = N'IDX_LPI_USRN' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4619CREATE INDEX [IDX_LPI_USRN] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([USRN]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4620GO
4621IF @@ERROR<>0
4622Begin
4623 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4624 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.IDX_LPI_USRN')
4625END
4626GO
4627
4628IF @@TRANCOUNT=0 BEGIN TRANSACTION
4629GO
4630if not exists (select * from sys.indexes where [name] = N'IndxLPILOGSTATUS' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4631CREATE INDEX [IndxLPILOGSTATUS] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([LOGICAL_STATUS]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4632GO
4633IF @@ERROR<>0
4634Begin
4635 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4636 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.IndxLPILOGSTATUS')
4637END
4638GO
4639
4640IF @@TRANCOUNT=0 BEGIN TRANSACTION
4641GO
4642if not exists (select * from sys.indexes where [name] = N'IndxLPIPOSTCODE' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4643CREATE INDEX [IndxLPIPOSTCODE] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([POSTCODE]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4644GO
4645IF @@ERROR<>0
4646Begin
4647 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4648 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.IndxLPIPOSTCODE')
4649END
4650GO
4651
4652IF @@TRANCOUNT=0 BEGIN TRANSACTION
4653GO
4654if not exists (select * from sys.indexes where [name] = N'IndxLPIUPRN' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4655CREATE INDEX [IndxLPIUPRN] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([UPRN]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4656GO
4657IF @@ERROR<>0
4658Begin
4659 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4660 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.IndxLPIUPRN')
4661END
4662GO
4663
4664IF @@TRANCOUNT=0 BEGIN TRANSACTION
4665GO
4666if not exists (select * from sys.indexes where [name] = N'indxPAON' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4667CREATE INDEX [indxPAON] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([PAO_FROM],[PAO_FROM_SUFF],[PAO_TO],[PAO_TO_SUFF],[PAO_NAME]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4668GO
4669IF @@ERROR<>0
4670Begin
4671 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4672 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.indxPAON')
4673END
4674GO
4675
4676IF @@TRANCOUNT=0 BEGIN TRANSACTION
4677GO
4678if not exists (select * from sys.indexes where [name] = N'indxSAON' and [object_id] = object_id(N'[dbo].[LAND_AND_PROPERTY_IDENTIFIER]'))
4679CREATE INDEX [indxSAON] ON [dbo].[LAND_AND_PROPERTY_IDENTIFIER] ([SAO_FROM],[SAO_FROM_SUFF],[SAO_TO],[SAO_TO_SUFF],[SAO_NAME]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4680GO
4681IF @@ERROR<>0
4682Begin
4683 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4684 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.LAND_AND_PROPERTY_IDENTIFIER.indxSAON')
4685END
4686GO
4687
4688IF @@TRANCOUNT=0 BEGIN TRANSACTION
4689GO
4690if not exists (select * from sys.indexes where [name] = N'IDX_ORGANISATION_UPRN' and [object_id] = object_id(N'[dbo].[ORGANISATION]'))
4691CREATE INDEX [IDX_ORGANISATION_UPRN] ON [dbo].[ORGANISATION] ([UPRN]) WITH (FILLFACTOR = 90) ON [PRIMARY];
4692GO
4693IF @@ERROR<>0
4694Begin
4695 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4696 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.ORGANISATION.IDX_ORGANISATION_UPRN')
4697END
4698GO
4699
4700IF @@TRANCOUNT=0 BEGIN TRANSACTION
4701GO
4702if not exists (select * from sys.indexes where [name] = N'SIDX_BLPU_GEOMETRY' and [object_id] = object_id(N'[dbo].[BLPU]'))
4703CREATE SPATIAL INDEX [SIDX_BLPU_GEOMETRY] ON [dbo].[BLPU] ([GEOMETRY])
4704USING GEOMETRY_GRID
4705WITH (BOUNDING_BOX = (0, 0, 700000, 1300000), GRIDS =(LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16) ;
4706GO
4707IF @@ERROR<>0
4708Begin
4709 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4710 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.BLPU.SIDX_BLPU_GEOMETRY')
4711END
4712GO
4713
4714IF @@TRANCOUNT=0 BEGIN TRANSACTION
4715GO
4716if not exists (select * from sys.indexes where [name] = N'SIDX_STREET_SP_GEOMETRY' and [object_id] = object_id(N'[dbo].[STREET_SP]'))
4717CREATE SPATIAL INDEX [SIDX_STREET_SP_GEOMETRY] ON [dbo].[STREET_SP] ([GEOMETRY])
4718USING GEOMETRY_GRID
4719WITH (BOUNDING_BOX = (0, 0, 700000, 1300000), GRIDS =(LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16) ;
4720GO
4721IF @@ERROR<>0
4722Begin
4723 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4724 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.STREET_SP.SIDX_STREET_SP_GEOMETRY')
4725END
4726GO
4727
4728IF @@TRANCOUNT=0 BEGIN TRANSACTION
4729GO
4730if not exists (select * from sys.indexes where [name] = N'SIDX_ESU_GEOMETRY' and [object_id] = object_id(N'[dbo].[ESU]'))
4731CREATE SPATIAL INDEX [SIDX_ESU_GEOMETRY] ON [dbo].[ESU] ([GEOMETRY])
4732USING GEOMETRY_GRID
4733WITH (BOUNDING_BOX = (0, 0, 700000, 1300000), GRIDS =(LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16) ;
4734GO
4735IF @@ERROR<>0
4736Begin
4737 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4738 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.ESU.SIDX_ESU_GEOMETRY')
4739END
4740GO
4741
4742IF @@TRANCOUNT=0 BEGIN TRANSACTION
4743GO
4744if not exists (select * from sys.indexes where [name] = N'SIDX_NODE_GEOMETRY' and [object_id] = object_id(N'[dbo].[NODE]'))
4745CREATE SPATIAL INDEX [SIDX_NODE_GEOMETRY] ON [dbo].[NODE] ([GEOMETRY])
4746USING GEOMETRY_GRID
4747WITH (BOUNDING_BOX = (0, 0, 700000, 1300000), GRIDS =(LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16) ;
4748GO
4749IF @@ERROR<>0
4750Begin
4751 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4752 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add index dbo.NODE.SIDX_NODE_GEOMETRY')
4753END
4754GO
4755
4756IF @@TRANCOUNT=0 BEGIN TRANSACTION
4757GO
4758SET ANSI_NULLS ON
4759GO
4760SET QUOTED_IDENTIFIER ON
4761GO
4762if not exists (select * from sys.objects where [name] = N'FK_ADDITIONAL_STREET_USRN' and [type] = 'F')
4763ALTER TABLE [dbo].[ADDITIONAL_STREET] WITH NOCHECK ADD
4764 CONSTRAINT [FK_ADDITIONAL_STREET_USRN] FOREIGN KEY
4765 (
4766 [USRN]
4767 )REFERENCES [dbo].[STREET](
4768 [USRN]
4769 )ON UPDATE NO ACTION ON DELETE CASCADE
4770GO
4771IF @@ERROR<>0
4772Begin
4773 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4774 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.ADDITIONAL_STREET.FK_ADDITIONAL_STREET_USRN')
4775END
4776GO
4777IF @@TRANCOUNT=0 BEGIN TRANSACTION
4778GO
4779SET ANSI_NULLS ON
4780GO
4781SET QUOTED_IDENTIFIER ON
4782GO
4783if not exists (select * from sys.objects where [name] = N'FK_ASD_HISTORY_USRN' and [type] = 'F')
4784ALTER TABLE [dbo].[ASD_HISTORY] ADD
4785 CONSTRAINT [FK_ASD_HISTORY_USRN] FOREIGN KEY
4786 (
4787 [USRN]
4788 )REFERENCES [dbo].[STREET](
4789 [USRN]
4790 )ON UPDATE NO ACTION ON DELETE CASCADE
4791GO
4792IF @@ERROR<>0
4793Begin
4794 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4795 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.ASD_HISTORY.FK_ASD_HISTORY_USRN')
4796END
4797GO
4798IF @@TRANCOUNT=0 BEGIN TRANSACTION
4799GO
4800SET ANSI_NULLS ON
4801GO
4802SET QUOTED_IDENTIFIER ON
4803GO
4804if not exists (select * from sys.objects where [name] = N'fk_STREET_XREF_XUSRN' and [type] = 'F')
4805ALTER TABLE [dbo].[STREET_CROSS_REFERENCE] WITH NOCHECK ADD
4806 CONSTRAINT [fk_STREET_XREF_XUSRN] FOREIGN KEY
4807 (
4808 [USRN]
4809 )REFERENCES [dbo].[STREET](
4810 [USRN]
4811 )ON UPDATE NO ACTION ON DELETE CASCADE
4812GO
4813IF @@ERROR<>0
4814Begin
4815 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4816 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.STREET_CROSS_REFERENCE.fk_STREET_XREF_XUSRN')
4817END
4818GO
4819IF @@TRANCOUNT=0 BEGIN TRANSACTION
4820GO
4821SET ANSI_NULLS ON
4822GO
4823SET QUOTED_IDENTIFIER ON
4824GO
4825if not exists (select * from sys.objects where [name] = N'FK_ALT_STREET_LPIUNIQUE' and [type] = 'F')
4826ALTER TABLE [dbo].[ALT_STREET_LPI] ADD
4827 CONSTRAINT [FK_ALT_STREET_LPIUNIQUE] FOREIGN KEY
4828 (
4829 [LPI_UNIQUE]
4830 )REFERENCES [dbo].[LAND_AND_PROPERTY_IDENTIFIER](
4831 [LPI_UNIQUE]
4832 )ON UPDATE NO ACTION ON DELETE CASCADE
4833GO
4834IF @@ERROR<>0
4835Begin
4836 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4837 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.ALT_STREET_LPI.FK_ALT_STREET_LPIUNIQUE')
4838END
4839GO
4840IF @@TRANCOUNT=0 BEGIN TRANSACTION
4841GO
4842SET ANSI_NULLS ON
4843GO
4844SET QUOTED_IDENTIFIER ON
4845GO
4846if not exists (select * from sys.objects where [name] = N'fk_STREET_DESCIPTOR_AAN' and [type] = 'F')
4847ALTER TABLE [dbo].[STREET_DESCRIPTOR] WITH NOCHECK ADD
4848 CONSTRAINT [fk_STREET_DESCIPTOR_AAN] FOREIGN KEY
4849 (
4850 [AAN_REF]
4851 )REFERENCES [dbo].[ADMIN_AREA_NAME](
4852 [AAN_REF]
4853 )ON UPDATE NO ACTION ON DELETE CASCADE
4854GO
4855IF @@ERROR<>0
4856Begin
4857 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4858 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.STREET_DESCRIPTOR.fk_STREET_DESCIPTOR_AAN')
4859END
4860GO
4861IF @@TRANCOUNT=0 BEGIN TRANSACTION
4862GO
4863SET ANSI_NULLS ON
4864GO
4865SET QUOTED_IDENTIFIER ON
4866GO
4867if not exists (select * from sys.objects where [name] = N'fk_STREET_DESCIPTOR_LOCALITY' and [type] = 'F')
4868ALTER TABLE [dbo].[STREET_DESCRIPTOR] WITH NOCHECK ADD
4869 CONSTRAINT [fk_STREET_DESCIPTOR_LOCALITY] FOREIGN KEY
4870 (
4871 [LOC_REF]
4872 )REFERENCES [dbo].[LOCALITY](
4873 [LOC_REF]
4874 )ON UPDATE NO ACTION ON DELETE CASCADE
4875GO
4876IF @@ERROR<>0
4877Begin
4878 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4879 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.STREET_DESCRIPTOR.fk_STREET_DESCIPTOR_LOCALITY')
4880END
4881GO
4882IF @@TRANCOUNT=0 BEGIN TRANSACTION
4883GO
4884SET ANSI_NULLS ON
4885GO
4886SET QUOTED_IDENTIFIER ON
4887GO
4888if not exists (select * from sys.objects where [name] = N'fk_STREET_DESCIPTOR_TOWN' and [type] = 'F')
4889ALTER TABLE [dbo].[STREET_DESCRIPTOR] WITH NOCHECK ADD
4890 CONSTRAINT [fk_STREET_DESCIPTOR_TOWN] FOREIGN KEY
4891 (
4892 [TOWN_REF]
4893 )REFERENCES [dbo].[TOWN](
4894 [TOWN_REF]
4895 )ON UPDATE NO ACTION ON DELETE CASCADE
4896GO
4897IF @@ERROR<>0
4898Begin
4899 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4900 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.STREET_DESCRIPTOR.fk_STREET_DESCIPTOR_TOWN')
4901END
4902GO
4903IF @@TRANCOUNT=0 BEGIN TRANSACTION
4904GO
4905SET ANSI_NULLS ON
4906GO
4907SET QUOTED_IDENTIFIER ON
4908GO
4909if not exists (select * from sys.objects where [name] = N'fk_Street_history_USRN' and [type] = 'F')
4910ALTER TABLE [dbo].[STREET_HISTORY] WITH NOCHECK ADD
4911 CONSTRAINT [fk_Street_history_USRN] FOREIGN KEY
4912 (
4913 [USRN]
4914 )REFERENCES [dbo].[STREET](
4915 [USRN]
4916 )ON UPDATE NO ACTION ON DELETE CASCADE
4917GO
4918IF @@ERROR<>0
4919Begin
4920 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4921 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.STREET_HISTORY.fk_Street_history_USRN')
4922END
4923GO
4924IF @@TRANCOUNT=0 BEGIN TRANSACTION
4925GO
4926SET ANSI_NULLS ON
4927GO
4928SET QUOTED_IDENTIFIER ON
4929GO
4930if not exists (select * from sys.objects where [name] = N'FKSTREETHISTORY_USRN' and [type] = 'F')
4931ALTER TABLE [dbo].[STREET_HISTORY] WITH NOCHECK ADD
4932 CONSTRAINT [FKSTREETHISTORY_USRN] FOREIGN KEY
4933 (
4934 [USRN]
4935 )REFERENCES [dbo].[STREET](
4936 [USRN]
4937 )ON UPDATE NO ACTION ON DELETE NO ACTION
4938GO
4939IF @@ERROR<>0
4940Begin
4941 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4942 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.STREET_HISTORY.FKSTREETHISTORY_USRN')
4943END
4944GO
4945IF @@TRANCOUNT=0 BEGIN TRANSACTION
4946GO
4947SET ANSI_NULLS ON
4948GO
4949SET QUOTED_IDENTIFIER ON
4950GO
4951if not exists (select * from sys.objects where [name] = N'FK_STREET_DESC_HISTORY_USRN' and [type] = 'F')
4952ALTER TABLE [dbo].[STREET_DESC_HISTORY] ADD
4953 CONSTRAINT [FK_STREET_DESC_HISTORY_USRN] FOREIGN KEY
4954 (
4955 [USRN]
4956 )REFERENCES [dbo].[STREET](
4957 [USRN]
4958 )ON UPDATE NO ACTION ON DELETE CASCADE
4959GO
4960IF @@ERROR<>0
4961Begin
4962 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4963 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.STREET_DESC_HISTORY.FK_STREET_DESC_HISTORY_USRN')
4964END
4965GO
4966IF @@TRANCOUNT=0 BEGIN TRANSACTION
4967GO
4968SET ANSI_NULLS ON
4969GO
4970SET QUOTED_IDENTIFIER ON
4971GO
4972if not exists (select * from sys.objects where [name] = N'fk_AppXRef_LPIUnique' and [type] = 'F')
4973ALTER TABLE [dbo].[APPLICATION_CROSS_REFERENCE] ADD
4974 CONSTRAINT [fk_AppXRef_LPIUnique] FOREIGN KEY
4975 (
4976 [LPI_UNIQUE]
4977 )REFERENCES [dbo].[LAND_AND_PROPERTY_IDENTIFIER](
4978 [LPI_UNIQUE]
4979 )ON UPDATE NO ACTION ON DELETE CASCADE
4980GO
4981IF @@ERROR<>0
4982Begin
4983 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
4984 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.APPLICATION_CROSS_REFERENCE.fk_AppXRef_LPIUnique')
4985END
4986GO
4987IF @@TRANCOUNT=0 BEGIN TRANSACTION
4988GO
4989SET ANSI_NULLS ON
4990GO
4991SET QUOTED_IDENTIFIER ON
4992GO
4993if not exists (select * from sys.objects where [name] = N'fk_APPXREF_SOURCE' and [type] = 'F')
4994ALTER TABLE [dbo].[APPLICATION_CROSS_REFERENCE] ADD
4995 CONSTRAINT [fk_APPXREF_SOURCE] FOREIGN KEY
4996 (
4997 [SOURCE]
4998 )REFERENCES [dbo].[APPLICATION_SOURCE](
4999 [SOURCE]
5000 )ON UPDATE NO ACTION ON DELETE CASCADE
5001GO
5002IF @@ERROR<>0
5003Begin
5004 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5005 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.APPLICATION_CROSS_REFERENCE.fk_APPXREF_SOURCE')
5006END
5007GO
5008IF @@TRANCOUNT=0 BEGIN TRANSACTION
5009GO
5010SET ANSI_NULLS ON
5011GO
5012SET QUOTED_IDENTIFIER ON
5013GO
5014if not exists (select * from sys.objects where [name] = N'FK_BLPU_PARISH' and [type] = 'F')
5015ALTER TABLE [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] WITH NOCHECK ADD
5016 CONSTRAINT [FK_BLPU_PARISH] FOREIGN KEY
5017 (
5018 [PARISHCODE]
5019 )REFERENCES [dbo].[PARISH](
5020 [PARISHCODE]
5021 )ON UPDATE NO ACTION ON DELETE CASCADE
5022GO
5023IF @@ERROR<>0
5024Begin
5025 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5026 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.BASIC_LAND_AND_PROPERTY_UNIT.FK_BLPU_PARISH')
5027END
5028GO
5029IF @@TRANCOUNT=0 BEGIN TRANSACTION
5030GO
5031SET ANSI_NULLS ON
5032GO
5033SET QUOTED_IDENTIFIER ON
5034GO
5035if not exists (select * from sys.objects where [name] = N'FK_BLPU_WARD' and [type] = 'F')
5036ALTER TABLE [dbo].[BASIC_LAND_AND_PROPERTY_UNIT] WITH NOCHECK ADD
5037 CONSTRAINT [FK_BLPU_WARD] FOREIGN KEY
5038 (
5039 [WARDCODE]
5040 )REFERENCES [dbo].[WARD](
5041 [WARDCODE]
5042 )ON UPDATE NO ACTION ON DELETE CASCADE
5043GO
5044IF @@ERROR<>0
5045Begin
5046 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5047 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.BASIC_LAND_AND_PROPERTY_UNIT.FK_BLPU_WARD')
5048END
5049GO
5050IF @@TRANCOUNT=0 BEGIN TRANSACTION
5051GO
5052SET ANSI_NULLS ON
5053GO
5054SET QUOTED_IDENTIFIER ON
5055GO
5056if not exists (select * from sys.objects where [name] = N'FK_BLPU_HISTORY_UPRN' and [type] = 'F')
5057ALTER TABLE [dbo].[BLPU_HISTORY] ADD
5058 CONSTRAINT [FK_BLPU_HISTORY_UPRN] FOREIGN KEY
5059 (
5060 [UPRN]
5061 )REFERENCES [dbo].[BASIC_LAND_AND_PROPERTY_UNIT](
5062 [UPRN]
5063 )ON UPDATE NO ACTION ON DELETE CASCADE
5064GO
5065IF @@ERROR<>0
5066Begin
5067 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5068 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.BLPU_HISTORY.FK_BLPU_HISTORY_UPRN')
5069END
5070GO
5071IF @@TRANCOUNT=0 BEGIN TRANSACTION
5072GO
5073SET ANSI_NULLS ON
5074GO
5075SET QUOTED_IDENTIFIER ON
5076GO
5077if not exists (select * from sys.objects where [name] = N'fK_LPI_HISTORY_LPIUNIQUE' and [type] = 'F')
5078ALTER TABLE [dbo].[LPI_HISTORY] ADD
5079 CONSTRAINT [fK_LPI_HISTORY_LPIUNIQUE] FOREIGN KEY
5080 (
5081 [LPI_UNIQUE]
5082 )REFERENCES [dbo].[LAND_AND_PROPERTY_IDENTIFIER](
5083 [LPI_UNIQUE]
5084 )ON UPDATE NO ACTION ON DELETE CASCADE
5085GO
5086IF @@ERROR<>0
5087Begin
5088 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5089 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.LPI_HISTORY.fK_LPI_HISTORY_LPIUNIQUE')
5090END
5091GO
5092IF @@TRANCOUNT=0 BEGIN TRANSACTION
5093GO
5094SET ANSI_NULLS ON
5095GO
5096SET QUOTED_IDENTIFIER ON
5097GO
5098if not exists (select * from sys.objects where [name] = N'FK_ESU_COORD_ESU_ID' and [type] = 'F')
5099ALTER TABLE [dbo].[ESU_COORD] ADD
5100 CONSTRAINT [FK_ESU_COORD_ESU_ID] FOREIGN KEY
5101 (
5102 [ESU_ID]
5103 )REFERENCES [dbo].[ELEMENTARY_STREET_UNIT](
5104 [ESU_ID]
5105 )ON UPDATE NO ACTION ON DELETE CASCADE
5106GO
5107IF @@ERROR<>0
5108Begin
5109 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5110 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.ESU_COORD.FK_ESU_COORD_ESU_ID')
5111END
5112GO
5113IF @@TRANCOUNT=0 BEGIN TRANSACTION
5114GO
5115SET ANSI_NULLS ON
5116GO
5117SET QUOTED_IDENTIFIER ON
5118GO
5119if not exists (select * from sys.objects where [name] = N'FK_BLPU_EXTENT_POLYGON_UPRN' and [type] = 'F')
5120ALTER TABLE [dbo].[BLPU_EXTENT_POLYGON] ADD
5121 CONSTRAINT [FK_BLPU_EXTENT_POLYGON_UPRN] FOREIGN KEY
5122 (
5123 [UPRN]
5124 )REFERENCES [dbo].[BASIC_LAND_AND_PROPERTY_UNIT](
5125 [UPRN]
5126 )ON UPDATE NO ACTION ON DELETE CASCADE
5127GO
5128IF @@ERROR<>0
5129Begin
5130 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5131 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.BLPU_EXTENT_POLYGON.FK_BLPU_EXTENT_POLYGON_UPRN')
5132END
5133GO
5134IF @@TRANCOUNT=0 BEGIN TRANSACTION
5135GO
5136SET ANSI_NULLS ON
5137GO
5138SET QUOTED_IDENTIFIER ON
5139GO
5140if not exists (select * from sys.objects where [name] = N'FK_BLPU_EXTENT_POLY_VER_UPRN' and [type] = 'F')
5141ALTER TABLE [dbo].[BLPU_EXTENT_POLYGON_VERTEX] ADD
5142 CONSTRAINT [FK_BLPU_EXTENT_POLY_VER_UPRN] FOREIGN KEY
5143 (
5144 [UPRN]
5145 )REFERENCES [dbo].[BASIC_LAND_AND_PROPERTY_UNIT](
5146 [UPRN]
5147 )ON UPDATE NO ACTION ON DELETE CASCADE
5148GO
5149IF @@ERROR<>0
5150Begin
5151 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5152 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.BLPU_EXTENT_POLYGON_VERTEX.FK_BLPU_EXTENT_POLY_VER_UPRN')
5153END
5154GO
5155IF @@TRANCOUNT=0 BEGIN TRANSACTION
5156GO
5157SET ANSI_NULLS ON
5158GO
5159SET QUOTED_IDENTIFIER ON
5160GO
5161if not exists (select * from sys.objects where [name] = N'FK_LPI_POSTALADR_LPI_UNIQUE' and [type] = 'F')
5162ALTER TABLE [dbo].[LPI_POSTALADR] ADD
5163 CONSTRAINT [FK_LPI_POSTALADR_LPI_UNIQUE] FOREIGN KEY
5164 (
5165 [LPI_UNIQUE]
5166 )REFERENCES [dbo].[LAND_AND_PROPERTY_IDENTIFIER](
5167 [LPI_UNIQUE]
5168 )ON UPDATE NO ACTION ON DELETE CASCADE
5169GO
5170IF @@ERROR<>0
5171Begin
5172 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5173 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.LPI_POSTALADR.FK_LPI_POSTALADR_LPI_UNIQUE')
5174END
5175GO
5176IF @@TRANCOUNT=0 BEGIN TRANSACTION
5177GO
5178SET ANSI_NULLS ON
5179GO
5180SET QUOTED_IDENTIFIER ON
5181GO
5182if not exists (select * from sys.objects where [name] = N'FK_ESU_CROSS_REFERENCE_ESU_ID' and [type] = 'F')
5183ALTER TABLE [dbo].[ESU_CROSS_REFERENCE] WITH NOCHECK ADD
5184 CONSTRAINT [FK_ESU_CROSS_REFERENCE_ESU_ID] FOREIGN KEY
5185 (
5186 [ESU_ID]
5187 )REFERENCES [dbo].[ELEMENTARY_STREET_UNIT](
5188 [ESU_ID]
5189 )ON UPDATE NO ACTION ON DELETE CASCADE
5190GO
5191IF @@ERROR<>0
5192Begin
5193 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5194 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.ESU_CROSS_REFERENCE.FK_ESU_CROSS_REFERENCE_ESU_ID')
5195END
5196GO
5197IF @@TRANCOUNT=0 BEGIN TRANSACTION
5198GO
5199SET ANSI_NULLS ON
5200GO
5201SET QUOTED_IDENTIFIER ON
5202GO
5203if not exists (select * from sys.objects where [name] = N'FK_ESU_CROSS_REFERENCE_USRN' and [type] = 'F')
5204ALTER TABLE [dbo].[ESU_CROSS_REFERENCE] WITH NOCHECK ADD
5205 CONSTRAINT [FK_ESU_CROSS_REFERENCE_USRN] FOREIGN KEY
5206 (
5207 [USRN]
5208 )REFERENCES [dbo].[STREET](
5209 [USRN]
5210 )ON UPDATE NO ACTION ON DELETE CASCADE
5211GO
5212IF @@ERROR<>0
5213Begin
5214 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5215 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.ESU_CROSS_REFERENCE.FK_ESU_CROSS_REFERENCE_USRN')
5216END
5217GO
5218IF @@TRANCOUNT=0 BEGIN TRANSACTION
5219GO
5220SET ANSI_NULLS ON
5221GO
5222SET QUOTED_IDENTIFIER ON
5223GO
5224if not exists (select * from sys.objects where [name] = N'FK_HEIGHTWIDTHWEIGHT_USRN' and [type] = 'F')
5225ALTER TABLE [dbo].[HEIGHTWIDTHWEIGHT] WITH NOCHECK ADD
5226 CONSTRAINT [FK_HEIGHTWIDTHWEIGHT_USRN] FOREIGN KEY
5227 (
5228 [USRN]
5229 )REFERENCES [dbo].[STREET](
5230 [USRN]
5231 )ON UPDATE NO ACTION ON DELETE CASCADE
5232GO
5233IF @@ERROR<>0
5234Begin
5235 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5236 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.HEIGHTWIDTHWEIGHT.FK_HEIGHTWIDTHWEIGHT_USRN')
5237END
5238GO
5239IF @@TRANCOUNT=0 BEGIN TRANSACTION
5240GO
5241SET ANSI_NULLS ON
5242GO
5243SET QUOTED_IDENTIFIER ON
5244GO
5245if not exists (select * from sys.objects where [name] = N'FK_ESU_HISTORY_ESU_ID' and [type] = 'F')
5246ALTER TABLE [dbo].[ESU_HISTORY] WITH NOCHECK ADD
5247 CONSTRAINT [FK_ESU_HISTORY_ESU_ID] FOREIGN KEY
5248 (
5249 [ESU_ID]
5250 )REFERENCES [dbo].[ELEMENTARY_STREET_UNIT](
5251 [ESU_ID]
5252 )ON UPDATE NO ACTION ON DELETE CASCADE
5253GO
5254IF @@ERROR<>0
5255Begin
5256 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5257 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.ESU_HISTORY.FK_ESU_HISTORY_ESU_ID')
5258END
5259GO
5260IF @@TRANCOUNT=0 BEGIN TRANSACTION
5261GO
5262SET ANSI_NULLS ON
5263GO
5264SET QUOTED_IDENTIFIER ON
5265GO
5266if not exists (select * from sys.objects where [name] = N'FK_LPI_ALIASID' and [type] = 'F')
5267ALTER TABLE [dbo].[LAND_AND_PROPERTY_IDENTIFIER] WITH NOCHECK ADD
5268 CONSTRAINT [FK_LPI_ALIASID] FOREIGN KEY
5269 (
5270 [ALIAS_ID]
5271 )REFERENCES [dbo].[ALIASINFO](
5272 [ALIAS_ID]
5273 )ON UPDATE NO ACTION ON DELETE NO ACTION
5274GO
5275IF @@ERROR<>0
5276Begin
5277 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5278 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.LAND_AND_PROPERTY_IDENTIFIER.FK_LPI_ALIASID')
5279END
5280GO
5281IF @@TRANCOUNT=0 BEGIN TRANSACTION
5282GO
5283SET ANSI_NULLS ON
5284GO
5285SET QUOTED_IDENTIFIER ON
5286GO
5287if not exists (select * from sys.objects where [name] = N'FK_LPI_POLICEBEATCODE' and [type] = 'F')
5288ALTER TABLE [dbo].[LAND_AND_PROPERTY_IDENTIFIER] WITH NOCHECK ADD
5289 CONSTRAINT [FK_LPI_POLICEBEATCODE] FOREIGN KEY
5290 (
5291 [POLICEBEATCODE]
5292 )REFERENCES [dbo].[POLICEBEATCODE](
5293 [POLICEBEATCODEID]
5294 )ON UPDATE NO ACTION ON DELETE CASCADE
5295GO
5296IF @@ERROR<>0
5297Begin
5298 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5299 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.LAND_AND_PROPERTY_IDENTIFIER.FK_LPI_POLICEBEATCODE')
5300END
5301GO
5302IF @@TRANCOUNT=0 BEGIN TRANSACTION
5303GO
5304SET ANSI_NULLS ON
5305GO
5306SET QUOTED_IDENTIFIER ON
5307GO
5308if not exists (select * from sys.objects where [name] = N'FK_LPI_POLICEFORCEAREA' and [type] = 'F')
5309ALTER TABLE [dbo].[LAND_AND_PROPERTY_IDENTIFIER] WITH NOCHECK ADD
5310 CONSTRAINT [FK_LPI_POLICEFORCEAREA] FOREIGN KEY
5311 (
5312 [POLICEFORCEAREA]
5313 )REFERENCES [dbo].[POLICEFORCEAREA](
5314 [POLICEFORCEAREAID]
5315 )ON UPDATE NO ACTION ON DELETE CASCADE
5316GO
5317IF @@ERROR<>0
5318Begin
5319 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5320 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.LAND_AND_PROPERTY_IDENTIFIER.FK_LPI_POLICEFORCEAREA')
5321END
5322GO
5323IF @@TRANCOUNT=0 BEGIN TRANSACTION
5324GO
5325SET ANSI_NULLS ON
5326GO
5327SET QUOTED_IDENTIFIER ON
5328GO
5329if not exists (select * from sys.objects where [name] = N'fk_LPI_UPRN' and [type] = 'F')
5330ALTER TABLE [dbo].[LAND_AND_PROPERTY_IDENTIFIER] WITH NOCHECK ADD
5331 CONSTRAINT [fk_LPI_UPRN] FOREIGN KEY
5332 (
5333 [UPRN]
5334 )REFERENCES [dbo].[BASIC_LAND_AND_PROPERTY_UNIT](
5335 [UPRN]
5336 )ON UPDATE NO ACTION ON DELETE CASCADE
5337GO
5338IF @@ERROR<>0
5339Begin
5340 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5341 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.LAND_AND_PROPERTY_IDENTIFIER.fk_LPI_UPRN')
5342END
5343GO
5344IF @@TRANCOUNT=0 BEGIN TRANSACTION
5345GO
5346SET ANSI_NULLS ON
5347GO
5348SET QUOTED_IDENTIFIER ON
5349GO
5350if not exists (select * from sys.objects where [name] = N'fk_LPI_USRN' and [type] = 'F')
5351ALTER TABLE [dbo].[LAND_AND_PROPERTY_IDENTIFIER] WITH NOCHECK ADD
5352 CONSTRAINT [fk_LPI_USRN] FOREIGN KEY
5353 (
5354 [USRN]
5355 )REFERENCES [dbo].[STREET](
5356 [USRN]
5357 )ON UPDATE NO ACTION ON DELETE CASCADE
5358GO
5359IF @@ERROR<>0
5360Begin
5361 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5362 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.LAND_AND_PROPERTY_IDENTIFIER.fk_LPI_USRN')
5363END
5364GO
5365IF @@TRANCOUNT=0 BEGIN TRANSACTION
5366GO
5367SET ANSI_NULLS ON
5368GO
5369SET QUOTED_IDENTIFIER ON
5370GO
5371if not exists (select * from sys.objects where [name] = N'FK_ESU_COORD_HISTORY_ESU_ID' and [type] = 'F')
5372ALTER TABLE [dbo].[ESU_COORD_HISTORY] WITH NOCHECK ADD
5373 CONSTRAINT [FK_ESU_COORD_HISTORY_ESU_ID] FOREIGN KEY
5374 (
5375 [ESU_ID]
5376 )REFERENCES [dbo].[ELEMENTARY_STREET_UNIT](
5377 [ESU_ID]
5378 )ON UPDATE NO ACTION ON DELETE CASCADE
5379GO
5380IF @@ERROR<>0
5381Begin
5382 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5383 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.ESU_COORD_HISTORY.FK_ESU_COORD_HISTORY_ESU_ID')
5384END
5385GO
5386IF @@TRANCOUNT=0 BEGIN TRANSACTION
5387GO
5388SET ANSI_NULLS ON
5389GO
5390SET QUOTED_IDENTIFIER ON
5391GO
5392if not exists (select * from sys.objects where [name] = N'FK_HWW_HISTORY' and [type] = 'F')
5393ALTER TABLE [dbo].[HWW_HISTORY] ADD
5394 CONSTRAINT [FK_HWW_HISTORY] FOREIGN KEY
5395 (
5396 [USRN],[HWW_SEQUENCE_NO],[AUTH_CODE]
5397 )REFERENCES [dbo].[HEIGHTWIDTHWEIGHT](
5398 [USRN],[HWW_SEQUENCE_NO],[AUTH_CODE]
5399 )ON UPDATE NO ACTION ON DELETE NO ACTION
5400GO
5401IF @@ERROR<>0
5402Begin
5403 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5404 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.HWW_HISTORY.FK_HWW_HISTORY')
5405END
5406GO
5407IF @@TRANCOUNT=0 BEGIN TRANSACTION
5408GO
5409SET ANSI_NULLS ON
5410GO
5411SET QUOTED_IDENTIFIER ON
5412GO
5413if not exists (select * from sys.objects where [name] = N'FK_HWW_HISTORY_USRN' and [type] = 'F')
5414ALTER TABLE [dbo].[HWW_HISTORY] ADD
5415 CONSTRAINT [FK_HWW_HISTORY_USRN] FOREIGN KEY
5416 (
5417 [USRN]
5418 )REFERENCES [dbo].[STREET](
5419 [USRN]
5420 )ON UPDATE NO ACTION ON DELETE CASCADE
5421GO
5422IF @@ERROR<>0
5423Begin
5424 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5425 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.HWW_HISTORY.FK_HWW_HISTORY_USRN')
5426END
5427GO
5428IF @@TRANCOUNT=0 BEGIN TRANSACTION
5429GO
5430SET ANSI_NULLS ON
5431GO
5432SET QUOTED_IDENTIFIER ON
5433GO
5434if not exists (select * from sys.objects where [name] = N'FK_REINST_DESIGNATION_USRN' and [type] = 'F')
5435ALTER TABLE [dbo].[REINST_DESIGNATION] WITH NOCHECK ADD
5436 CONSTRAINT [FK_REINST_DESIGNATION_USRN] FOREIGN KEY
5437 (
5438 [USRN]
5439 )REFERENCES [dbo].[STREET](
5440 [USRN]
5441 )ON UPDATE NO ACTION ON DELETE CASCADE
5442GO
5443IF @@ERROR<>0
5444Begin
5445 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5446 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.REINST_DESIGNATION.FK_REINST_DESIGNATION_USRN')
5447END
5448GO
5449IF @@TRANCOUNT=0 BEGIN TRANSACTION
5450GO
5451SET ANSI_NULLS ON
5452GO
5453SET QUOTED_IDENTIFIER ON
5454GO
5455if not exists (select * from sys.objects where [name] = N'FKREINSTUSRN' and [type] = 'F')
5456ALTER TABLE [dbo].[REINST_DESIGNATION] WITH NOCHECK ADD
5457 CONSTRAINT [FKREINSTUSRN] FOREIGN KEY
5458 (
5459 [USRN]
5460 )REFERENCES [dbo].[STREET](
5461 [USRN]
5462 )ON UPDATE NO ACTION ON DELETE NO ACTION
5463GO
5464IF @@ERROR<>0
5465Begin
5466 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5467 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.REINST_DESIGNATION.FKREINSTUSRN')
5468END
5469GO
5470IF @@TRANCOUNT=0 BEGIN TRANSACTION
5471GO
5472SET ANSI_NULLS ON
5473GO
5474SET QUOTED_IDENTIFIER ON
5475GO
5476if not exists (select * from sys.objects where [name] = N'FK_REINST_DESIG_HISTORY_USRN' and [type] = 'F')
5477ALTER TABLE [dbo].[REINST_DESIG_HISTORY] ADD
5478 CONSTRAINT [FK_REINST_DESIG_HISTORY_USRN] FOREIGN KEY
5479 (
5480 [USRN]
5481 )REFERENCES [dbo].[STREET](
5482 [USRN]
5483 )ON UPDATE NO ACTION ON DELETE CASCADE
5484GO
5485IF @@ERROR<>0
5486Begin
5487 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5488 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.REINST_DESIG_HISTORY.FK_REINST_DESIG_HISTORY_USRN')
5489END
5490GO
5491IF @@TRANCOUNT=0 BEGIN TRANSACTION
5492GO
5493SET ANSI_NULLS ON
5494GO
5495SET QUOTED_IDENTIFIER ON
5496GO
5497if not exists (select * from sys.objects where [name] = N'fk_esu_xref_history_ESUID' and [type] = 'F')
5498ALTER TABLE [dbo].[ESU_XREF_HISTORY] WITH NOCHECK ADD
5499 CONSTRAINT [fk_esu_xref_history_ESUID] FOREIGN KEY
5500 (
5501 [ESU_ID]
5502 )REFERENCES [dbo].[ELEMENTARY_STREET_UNIT](
5503 [ESU_ID]
5504 )ON UPDATE NO ACTION ON DELETE CASCADE
5505GO
5506IF @@ERROR<>0
5507Begin
5508 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5509 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.ESU_XREF_HISTORY.fk_esu_xref_history_ESUID')
5510END
5511GO
5512IF @@TRANCOUNT=0 BEGIN TRANSACTION
5513GO
5514SET ANSI_NULLS ON
5515GO
5516SET QUOTED_IDENTIFIER ON
5517GO
5518if not exists (select * from sys.objects where [name] = N'fk_esu_xref_history_USRN' and [type] = 'F')
5519ALTER TABLE [dbo].[ESU_XREF_HISTORY] WITH NOCHECK ADD
5520 CONSTRAINT [fk_esu_xref_history_USRN] FOREIGN KEY
5521 (
5522 [USRN]
5523 )REFERENCES [dbo].[STREET](
5524 [USRN]
5525 )ON UPDATE NO ACTION ON DELETE CASCADE
5526GO
5527IF @@ERROR<>0
5528Begin
5529 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5530 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.ESU_XREF_HISTORY.fk_esu_xref_history_USRN')
5531END
5532GO
5533IF @@TRANCOUNT=0 BEGIN TRANSACTION
5534GO
5535SET ANSI_NULLS ON
5536GO
5537SET QUOTED_IDENTIFIER ON
5538GO
5539if not exists (select * from sys.objects where [name] = N'fk_STREET_ALTAAN' and [type] = 'F')
5540ALTER TABLE [dbo].[STREET] WITH NOCHECK ADD
5541 CONSTRAINT [fk_STREET_ALTAAN] FOREIGN KEY
5542 (
5543 [ALTERNATE_AAN_REF]
5544 )REFERENCES [dbo].[ADMIN_AREA_NAME](
5545 [AAN_REF]
5546 )ON UPDATE NO ACTION ON DELETE CASCADE
5547GO
5548IF @@ERROR<>0
5549Begin
5550 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5551 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.STREET.fk_STREET_ALTAAN')
5552END
5553GO
5554IF @@TRANCOUNT=0 BEGIN TRANSACTION
5555GO
5556SET ANSI_NULLS ON
5557GO
5558SET QUOTED_IDENTIFIER ON
5559GO
5560if not exists (select * from sys.objects where [name] = N'fk_STREET_ALTLOCALITY' and [type] = 'F')
5561ALTER TABLE [dbo].[STREET] WITH NOCHECK ADD
5562 CONSTRAINT [fk_STREET_ALTLOCALITY] FOREIGN KEY
5563 (
5564 [ALTERNATE_LOC_REF]
5565 )REFERENCES [dbo].[LOCALITY](
5566 [LOC_REF]
5567 )ON UPDATE NO ACTION ON DELETE CASCADE
5568GO
5569IF @@ERROR<>0
5570Begin
5571 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5572 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.STREET.fk_STREET_ALTLOCALITY')
5573END
5574GO
5575IF @@TRANCOUNT=0 BEGIN TRANSACTION
5576GO
5577SET ANSI_NULLS ON
5578GO
5579SET QUOTED_IDENTIFIER ON
5580GO
5581if not exists (select * from sys.objects where [name] = N'fk_STREET_ALTTOWN' and [type] = 'F')
5582ALTER TABLE [dbo].[STREET] WITH NOCHECK ADD
5583 CONSTRAINT [fk_STREET_ALTTOWN] FOREIGN KEY
5584 (
5585 [ALTERNATE_TOWN_REF]
5586 )REFERENCES [dbo].[TOWN](
5587 [TOWN_REF]
5588 )ON UPDATE NO ACTION ON DELETE CASCADE
5589GO
5590IF @@ERROR<>0
5591Begin
5592 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5593 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.STREET.fk_STREET_ALTTOWN')
5594END
5595GO
5596IF @@TRANCOUNT=0 BEGIN TRANSACTION
5597GO
5598SET ANSI_NULLS ON
5599GO
5600SET QUOTED_IDENTIFIER ON
5601GO
5602if not exists (select * from sys.objects where [name] = N'fk_STREET_AUTHCODE' and [type] = 'F')
5603ALTER TABLE [dbo].[STREET] WITH NOCHECK ADD
5604 CONSTRAINT [fk_STREET_AUTHCODE] FOREIGN KEY
5605 (
5606 [AUTHORITY]
5607 )REFERENCES [dbo].[AUTHORITY](
5608 [AUTH_CODE]
5609 )ON UPDATE NO ACTION ON DELETE NO ACTION
5610GO
5611IF @@ERROR<>0
5612Begin
5613 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5614 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.STREET.fk_STREET_AUTHCODE')
5615END
5616GO
5617IF @@TRANCOUNT=0 BEGIN TRANSACTION
5618GO
5619SET ANSI_NULLS ON
5620GO
5621SET QUOTED_IDENTIFIER ON
5622GO
5623if not exists (select * from sys.objects where [name] = N'FK_SPECIAL_DESIGNATION_USRN' and [type] = 'F')
5624ALTER TABLE [dbo].[SPECIAL_DESIGNATION] WITH NOCHECK ADD
5625 CONSTRAINT [FK_SPECIAL_DESIGNATION_USRN] FOREIGN KEY
5626 (
5627 [USRN]
5628 )REFERENCES [dbo].[STREET](
5629 [USRN]
5630 )ON UPDATE NO ACTION ON DELETE CASCADE
5631GO
5632IF @@ERROR<>0
5633Begin
5634 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5635 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.SPECIAL_DESIGNATION.FK_SPECIAL_DESIGNATION_USRN')
5636END
5637GO
5638IF @@TRANCOUNT=0 BEGIN TRANSACTION
5639GO
5640SET ANSI_NULLS ON
5641GO
5642SET QUOTED_IDENTIFIER ON
5643GO
5644if not exists (select * from sys.objects where [name] = N'FKSPECIALDESIGUSRN' and [type] = 'F')
5645ALTER TABLE [dbo].[SPECIAL_DESIGNATION] WITH NOCHECK ADD
5646 CONSTRAINT [FKSPECIALDESIGUSRN] FOREIGN KEY
5647 (
5648 [USRN]
5649 )REFERENCES [dbo].[STREET](
5650 [USRN]
5651 )ON UPDATE NO ACTION ON DELETE NO ACTION
5652GO
5653IF @@ERROR<>0
5654Begin
5655 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5656 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.SPECIAL_DESIGNATION.FKSPECIALDESIGUSRN')
5657END
5658GO
5659IF @@TRANCOUNT=0 BEGIN TRANSACTION
5660GO
5661SET ANSI_NULLS ON
5662GO
5663SET QUOTED_IDENTIFIER ON
5664GO
5665if not exists (select * from sys.objects where [name] = N'FK_SPECIAL_DESIG_HISTORY_USRN' and [type] = 'F')
5666ALTER TABLE [dbo].[SPECIAL_DESIG_HISTORY] ADD
5667 CONSTRAINT [FK_SPECIAL_DESIG_HISTORY_USRN] FOREIGN KEY
5668 (
5669 [USRN]
5670 )REFERENCES [dbo].[STREET](
5671 [USRN]
5672 )ON UPDATE NO ACTION ON DELETE CASCADE
5673GO
5674IF @@ERROR<>0
5675Begin
5676 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5677 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.SPECIAL_DESIG_HISTORY.FK_SPECIAL_DESIG_HISTORY_USRN')
5678END
5679GO
5680IF @@TRANCOUNT=0 BEGIN TRANSACTION
5681GO
5682SET ANSI_NULLS ON
5683GO
5684SET QUOTED_IDENTIFIER ON
5685GO
5686if not exists (select * from sys.objects where [name] = N'FK_OWE_HISTORY' and [type] = 'F')
5687ALTER TABLE [dbo].[OWE_HISTORY] ADD
5688 CONSTRAINT [FK_OWE_HISTORY] FOREIGN KEY
5689 (
5690 [ESU_ID],[SEQUENCE_NUMBER],[AUTH_CODE]
5691 )REFERENCES [dbo].[ONEWAYSTREETEXEMPT](
5692 [ESU_ID],[SEQUENCE_NUMBER],[AUTH_CODE]
5693 )ON UPDATE NO ACTION ON DELETE NO ACTION
5694GO
5695IF @@ERROR<>0
5696Begin
5697 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5698 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.OWE_HISTORY.FK_OWE_HISTORY')
5699END
5700GO
5701IF @@TRANCOUNT=0 BEGIN TRANSACTION
5702GO
5703SET ANSI_NULLS ON
5704GO
5705SET QUOTED_IDENTIFIER ON
5706GO
5707if not exists (select * from sys.objects where [name] = N'FK_BLPU_EXTENT_UPRN' and [type] = 'F')
5708ALTER TABLE [dbo].[BLPU_EXTENT] ADD
5709 CONSTRAINT [FK_BLPU_EXTENT_UPRN] FOREIGN KEY
5710 (
5711 [UPRN]
5712 )REFERENCES [dbo].[BASIC_LAND_AND_PROPERTY_UNIT](
5713 [UPRN]
5714 )ON UPDATE NO ACTION ON DELETE CASCADE
5715GO
5716IF @@ERROR<>0
5717Begin
5718 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5719 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.BLPU_EXTENT.FK_BLPU_EXTENT_UPRN')
5720END
5721GO
5722IF @@TRANCOUNT=0 BEGIN TRANSACTION
5723GO
5724SET ANSI_NULLS ON
5725GO
5726SET QUOTED_IDENTIFIER ON
5727GO
5728if not exists (select * from sys.objects where [name] = N'FK_ORGANISATION_UPRN' and [type] = 'F')
5729ALTER TABLE [dbo].[ORGANISATION] ADD
5730 CONSTRAINT [FK_ORGANISATION_UPRN] FOREIGN KEY
5731 (
5732 [UPRN]
5733 )REFERENCES [dbo].[BASIC_LAND_AND_PROPERTY_UNIT](
5734 [UPRN]
5735 )ON UPDATE NO ACTION ON DELETE CASCADE
5736GO
5737IF @@ERROR<>0
5738Begin
5739 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5740 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.ORGANISATION.FK_ORGANISATION_UPRN')
5741END
5742GO
5743IF @@TRANCOUNT=0 BEGIN TRANSACTION
5744GO
5745SET ANSI_NULLS ON
5746GO
5747SET QUOTED_IDENTIFIER ON
5748GO
5749if not exists (select * from sys.objects where [name] = N'FK_PROVENANCE_UPRN' and [type] = 'F')
5750ALTER TABLE [dbo].[PROVENANCE] ADD
5751 CONSTRAINT [FK_PROVENANCE_UPRN] FOREIGN KEY
5752 (
5753 [UPRN]
5754 )REFERENCES [dbo].[BASIC_LAND_AND_PROPERTY_UNIT](
5755 [UPRN]
5756 )ON UPDATE NO ACTION ON DELETE CASCADE
5757GO
5758IF @@ERROR<>0
5759Begin
5760 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5761 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.PROVENANCE.FK_PROVENANCE_UPRN')
5762END
5763GO
5764IF @@TRANCOUNT=0 BEGIN TRANSACTION
5765GO
5766SET ANSI_NULLS ON
5767GO
5768SET QUOTED_IDENTIFIER ON
5769GO
5770if not exists (select * from sys.objects where [name] = N'FK_BLPU_CLASSIFICATION_UPRN' and [type] = 'F')
5771ALTER TABLE [dbo].[BLPU_CLASSIFICATION] ADD
5772 CONSTRAINT [FK_BLPU_CLASSIFICATION_UPRN] FOREIGN KEY
5773 (
5774 [UPRN]
5775 )REFERENCES [dbo].[BASIC_LAND_AND_PROPERTY_UNIT](
5776 [UPRN]
5777 )ON UPDATE NO ACTION ON DELETE CASCADE
5778GO
5779IF @@ERROR<>0
5780Begin
5781 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5782 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.BLPU_CLASSIFICATION.FK_BLPU_CLASSIFICATION_UPRN')
5783END
5784GO
5785IF @@TRANCOUNT=0 BEGIN TRANSACTION
5786GO
5787SET ANSI_NULLS ON
5788GO
5789SET QUOTED_IDENTIFIER ON
5790GO
5791if not exists (select * from sys.objects where [name] = N'FK_PROV_HISTORY_UPRN' and [type] = 'F')
5792ALTER TABLE [dbo].[PROV_HISTORY] ADD
5793 CONSTRAINT [FK_PROV_HISTORY_UPRN] FOREIGN KEY
5794 (
5795 [UPRN]
5796 )REFERENCES [dbo].[BASIC_LAND_AND_PROPERTY_UNIT](
5797 [UPRN]
5798 )ON UPDATE NO ACTION ON DELETE NO ACTION
5799GO
5800IF @@ERROR<>0
5801Begin
5802 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5803 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add foreign key dbo.PROV_HISTORY.FK_PROV_HISTORY_UPRN')
5804END
5805GO
5806IF @@TRANCOUNT=0 BEGIN TRANSACTION
5807GO
5808EXEC sys.sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
5809Begin DesignProperties =
5810 Begin PaneConfigurations =
5811 Begin PaneConfiguration = 0
5812 NumPanes = 4
5813 Configuration = "(H (1[41] 4[14] 2[23] 3) )"
5814 End
5815 Begin PaneConfiguration = 1
5816 NumPanes = 3
5817 Configuration = "(H (1 [50] 4 [25] 3))"
5818 End
5819 Begin PaneConfiguration = 2
5820 NumPanes = 3
5821 Configuration = "(H (1 [50] 2 [25] 3))"
5822 End
5823 Begin PaneConfiguration = 3
5824 NumPanes = 3
5825 Configuration = "(H (4 [30] 2 [40] 3))"
5826 End
5827 Begin PaneConfiguration = 4
5828 NumPanes = 2
5829 Configuration = "(H (1 [56] 3))"
5830 End
5831 Begin PaneConfiguration = 5
5832 NumPanes = 2
5833 Configuration = "(H (2 [66] 3))"
5834 End
5835 Begin PaneConfiguration = 6
5836 NumPanes = 2
5837 Configuration = "(H (4 [50] 3))"
5838 End
5839 Begin PaneConfiguration = 7
5840 NumPanes = 1
5841 Configuration = "(V (3))"
5842 End
5843 Begin PaneConfiguration = 8
5844 NumPanes = 3
5845 Configuration = "(H (1[56] 4[18] 2) )"
5846 End
5847 Begin PaneConfiguration = 9
5848 NumPanes = 2
5849 Configuration = "(H (1 [75] 4))"
5850 End
5851 Begin PaneConfiguration = 10
5852 NumPanes = 2
5853 Configuration = "(H (1[66] 2) )"
5854 End
5855 Begin PaneConfiguration = 11
5856 NumPanes = 2
5857 Configuration = "(H (4 [60] 2))"
5858 End
5859 Begin PaneConfiguration = 12
5860 NumPanes = 1
5861 Configuration = "(H (1) )"
5862 End
5863 Begin PaneConfiguration = 13
5864 NumPanes = 1
5865 Configuration = "(V (4))"
5866 End
5867 Begin PaneConfiguration = 14
5868 NumPanes = 1
5869 Configuration = "(V (2))"
5870 End
5871 ActivePaneConfig = 0
5872 End
5873 Begin DiagramPane =
5874 Begin Origin =
5875 Top = 0
5876 Left = 0
5877 End
5878 Begin Tables =
5879 Begin Table = "LAND_AND_PROPERTY_IDENTIFIER"
5880 Begin Extent =
5881 Top = 6
5882 Left = 38
5883 Bottom = 283
5884 Right = 264
5885 End
5886 DisplayFlags = 280
5887 TopColumn = 0
5888 End
5889 Begin Table = "STREET"
5890 Begin Extent =
5891 Top = 6
5892 Left = 302
5893 Bottom = 302
5894 Right = 518
5895 End
5896 DisplayFlags = 280
5897 TopColumn = 0
5898 End
5899 Begin Table = "TOWN"
5900 Begin Extent =
5901 Top = 6
5902 Left = 551
5903 Bottom = 310
5904 Right = 702
5905 End
5906 DisplayFlags = 280
5907 TopColumn = 0
5908 End
5909 End
5910 End
5911 Begin SQLPane =
5912 End
5913 Begin DataPane =
5914 Begin ParameterDefaults = ""
5915 End
5916 Begin ColumnWidths = 20
5917 Width = 284
5918 Width = 1500
5919 Width = 1500
5920 Width = 1500
5921 Width = 1500
5922 Width = 1500
5923 Width = 1500
5924 Width = 1500
5925 Width = 1500
5926 Width = 1500
5927 Width = 1500
5928 Width = 1500
5929 Width = 1500
5930 Width = 1500
5931 Width = 1500
5932 Width = 1500
5933 Width = 1500
5934 Width = 1500
5935 Width = 1500
5936 Width = 1500
5937 End
5938 End
5939 Begin CriteriaPane =
5940 Begin ColumnWidths = 11
5941 Column = 1440
5942 Alias = 900
5943 Table = 1170
5944 Output = 720
5945 Append = 1400
5946 NewValue = 1170
5947 SortType = 1350
5948 SortOrder = 1410
5949 GroupBy = 1350
5950 Filter = 1350
5951 Or = 1350
5952 Or = 1350
5953 Or = 1350
5954 ', 'SCHEMA', N'dbo', 'VIEW', N'ADDRESS_SEARCH', NULL, NULL
5955GO
5956
5957IF @@ERROR<>0
5958Begin
5959 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5960 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add extended properties')
5961END
5962GO
5963IF @@TRANCOUNT=0 BEGIN TRANSACTION
5964GO
5965EXEC sys.sp_addextendedproperty N'MS_DiagramPane2', N'End
5966 End
5967End
5968', 'SCHEMA', N'dbo', 'VIEW', N'ADDRESS_SEARCH', NULL, NULL
5969GO
5970
5971IF @@ERROR<>0
5972Begin
5973 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5974 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add extended properties')
5975END
5976GO
5977IF @@TRANCOUNT=0 BEGIN TRANSACTION
5978GO
5979DECLARE @EP_Value AS int
5980SET @EP_Value = 2
5981EXEC sys.sp_addextendedproperty N'MS_DiagramPaneCount', @EP_Value, 'SCHEMA', N'dbo', 'VIEW', N'ADDRESS_SEARCH', NULL, NULL
5982GO
5983
5984IF @@ERROR<>0
5985Begin
5986 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5987 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to add extended properties')
5988END
5989GO
5990IF @@TRANCOUNT=0 BEGIN TRANSACTION
5991GO
5992if exists (select * from sys.database_principals where name = N'nlpg')
5993GRANT CONNECT TO [nlpg]
5994IF @@ERROR<>0
5995Begin
5996 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
5997 INSERT INTO #ErrorLog (errno,errdescr) values(@@ERROR,'Failed to alter permissions')
5998END
5999GO
6000IF EXISTS (Select * from #ErrorLog)
6001BEGIN
6002 IF @@TRANCOUNT>0 ROLLBACK TRANSACTION
6003END
6004ELSE
6005BEGIN
6006 IF @@TRANCOUNT>0 COMMIT TRANSACTION
6007END
6008IF EXISTS (Select * from #ErrorLog)
6009BEGIN
6010 Print 'Database synchronization script failed'
6011 GOTO QuitWithErrors
6012END
6013ELSE
6014BEGIN
6015 Print 'Database synchronization completed successfully'
6016END
6017
6018
6019
6020QuitWithErrors: