TSQL Recipes: Generate Range from Granularity Values

Recently I am working on a few reporting tasks. Because the raw data from the data source is not formatted for reporting purpose, I have to prepare quite a lot scripts to transform the data. So in next few posts I will demonstrate some of them, hopefully they can help you in some ways.

Today's topic is about time range. So basically what happened is the data saved from front end application is in granularity level, but we need to combine then to form data range format. For example, John Smith works at 01:00 - 02:00, 02:00 - 03:00, 05:00 - 06:00, the output needs to be John Smith: 01:00 - 03:00, 05:00 - 06:00.

To demonstrate the script, firstly let's create a table variable to host some dummy data

use tempdb
go

--create a table variable to host dummy data
declare @tbl table (UserKey varchar(10), ScheduleDate date, StartTime time(0), EndTime time(0))

insert into @tbl
select 'JSmith', '20161201', '00:00', '01:00'
union all
select 'JSmith', '20161201', '01:00', '02:00'
union all
select 'JSmith', '20161201', '02:00', '03:00'
union all
select 'JSmith', '20161201', '04:00', '05:00'
union all
select 'JSmith', '20161201', '05:00', '06:00'
union all
select 'JSmith', '20161201', '06:00', '07:00'
union all
select 'JSmith', '20161201', '07:00', '08:00'
union all
select 'JSmith', '20161201', '17:00', '18:00'
union all
select 'JSmith', '20161201', '18:00', '19:00'
union all
select 'JSmith', '20161201', '19:00', '20:00'
union all
select 'JSmith', '20161202', '09:00', '10:00'
union all
select 'JSmith', '20161202', '10:00', '11:00'
union all
select 'JSmith', '20161202', '11:00', '12:00'
union all
select 'JSmith', '20161202', '13:00', '14:00'

select * from @tbl

What we have now is a batch of dummy data like this:

Now time to transform the dummy data into range form. Firstly we can use a left self join to get begin time for each range:

--find the start time, lets call it TS
select t1.*
from @tbl t1 
 left join @tbl t2 on t1.UserKey = t2.UserKey 
   and t1.ScheduleDate = t2.ScheduleDate 
   and DATEADD(HOUR, -1, t1.StartTime) = t2.StartTime 
where t2.UserKey is null

Now we do the same left self join, but this time change t1 and t2 position. So what we have is the end time for each range

--find the end time, lets call it TE 
select t1.*
from @tbl t1 
 left join @tbl t2 on t1.UserKey = t2.UserKey 
   and t1.ScheduleDate = t2.ScheduleDate 
   and t1.StartTime = DATEADD(HOUR, -1, t2.StartTime)
where t2.UserKey is null

If we run these two queries, we get below results:

From the result it is very clear what we need to do next:

• Join these two result sets by UserKey and ScheduleDate
• Use StartTime From ST result set as the begin time of the range
• From all StartTime From ET result set which are greater than the StartTime of ST result set, we use the first EndTime as the end time of the range

Well, I guess you are confused. So I make life simple for you :p

;with TS as
(
select t1.*
from @tbl t1 
 left join @tbl t2 on t1.UserKey = t2.UserKey 
  and t1.ScheduleDate = t2.ScheduleDate 
  and DATEADD(HOUR, -1, t1.StartTime) = t2.StartTime 
where t2.UserKey is null
),
TE as
(
select t1.*
from @tbl t1 
 left join @tbl t2 on t1.UserKey = t2.UserKey 
  and t1.ScheduleDate = t2.ScheduleDate 
  and t1.StartTime = DATEADD(HOUR, -1, t2.StartTime)
where t2.UserKey is null
)

select ts.UserKey, ts.ScheduleDate, ts.StartTime, MIN(te.EndTime) as EndTime
from TS join TE on ts.UserKey = te.UserKey and ts.ScheduleDate = te.ScheduleDate and ts.StartTime < te.StartTime
group by ts.UserKey, ts.ScheduleDate, ts.StartTime

Run the script and we get what we want. Enjoy.

G-NAF Bulk Import Script: build an Australia national address database

Address verification had never been a simple topic. It involves quite a lot resources if you want to achieve an acceptable result. Previously we had tried to use Australia PAF file for verification purpose. However it is not as good as we expected: the PAF file is for parcel delivery, so for instance, you can identify a P.O. box through the PAF file, but it cannot be treated as a residential address.

Now good news is, through years and years negotiation, PSMA finally opened its G-NAF (geo-coded national address file) to public this year. Additionally PSMA also confirmed the it will keep the G-NAF refreshed every 3 months (May, August, November, and so on). But because data.gov.au is being rebuilt, the latest version I could find is August version. Well, my purpose of this post is to help you import address data into SQL Server quickly. So if you want to know more about G-NAF, simply go to their website.

The G-NAF file downloaded from data.gov.au includes the T-SQL script for database setup as well as the script for constraints setup. However importing the raw data could be a bit trouble due to amount of tables and raw data files.

To make life easier, you can try below bulk load statements. I generated them by looping tables and file names, what you need to do is just replacing "{G-NAF Folder}" with your own folder. Hope they can save you a bit time. Enjoy.

 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\ACT_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\ACT_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\ACT_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\ACT_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\ACT_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\ACT_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\ACT_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\ACT_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\ACT_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\ACT_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\ACT_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\ACT_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\ACT_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\ACT_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\ACT_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\ACT_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\NSW_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\NSW_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\NSW_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\NSW_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\NSW_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\NSW_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\NSW_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\NSW_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\NSW_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\NSW_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\NSW_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\NSW_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\NSW_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\NSW_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\NSW_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\NSW_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\NT_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\NT_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\NT_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\NT_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\NT_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\NT_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\NT_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\NT_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\NT_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\NT_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\NT_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\NT_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\NT_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\NT_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\NT_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\NT_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\OT_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\OT_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\OT_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\OT_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\OT_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\OT_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\OT_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\OT_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\OT_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\OT_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\OT_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\OT_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\OT_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\OT_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\OT_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\OT_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\QLD_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\QLD_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\QLD_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\QLD_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\QLD_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\QLD_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\QLD_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\QLD_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\QLD_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\QLD_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\QLD_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\QLD_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\QLD_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\QLD_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\QLD_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\QLD_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\SA_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\SA_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\SA_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\SA_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\SA_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\SA_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\SA_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\SA_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\SA_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\SA_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\SA_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\SA_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\SA_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\SA_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\SA_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\SA_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\TAS_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\TAS_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\TAS_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\TAS_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\TAS_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\TAS_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\TAS_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\TAS_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\TAS_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\TAS_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\TAS_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\TAS_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\TAS_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\TAS_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\TAS_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\TAS_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\VIC_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\VIC_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\VIC_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\VIC_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\VIC_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\VIC_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\VIC_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\VIC_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\VIC_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\VIC_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\VIC_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\VIC_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\VIC_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\VIC_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\VIC_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\VIC_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_ALIAS From '{G-NAF Folder}\WA_ADDRESS_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DEFAULT_GEOCODE From '{G-NAF Folder}\WA_ADDRESS_DEFAULT_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_DETAIL From '{G-NAF Folder}\WA_ADDRESS_DETAIL_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_MESH_BLOCK_2011 From '{G-NAF Folder}\WA_ADDRESS_MESH_BLOCK_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE_GEOCODE From '{G-NAF Folder}\WA_ADDRESS_SITE_GEOCODE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.ADDRESS_SITE From '{G-NAF Folder}\WA_ADDRESS_SITE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_ALIAS From '{G-NAF Folder}\WA_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_NEIGHBOUR From '{G-NAF Folder}\WA_LOCALITY_NEIGHBOUR_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY_POINT From '{G-NAF Folder}\WA_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.LOCALITY From '{G-NAF Folder}\WA_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.MB_2011 From '{G-NAF Folder}\WA_MB_2011_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.PRIMARY_SECONDARY From '{G-NAF Folder}\WA_PRIMARY_SECONDARY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STATE From '{G-NAF Folder}\WA_STATE_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_ALIAS From '{G-NAF Folder}\WA_STREET_LOCALITY_ALIAS_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY_POINT From '{G-NAF Folder}\WA_STREET_LOCALITY_POINT_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);  
 bulk insert G_NAF.dbo.STREET_LOCALITY From '{G-NAF Folder}\WA_STREET_LOCALITY_psv.psv' with(FieldTerminator = '|', RowTerminator = '\n', FirstRow = 2);