UK Postcodes database

Managed to find a CSV on the Internet with all the details needed for the UK Postcodes (download it here). Decided to import it into a database, you can use the script below I made.... of course you could just use the import that comes with SQL.. but where's the fun in that?

CREATE TABLE Postcodes ([Postcode] VARCHAR(10), [IntDate] DATE, [GREast] VARCHAR(5), [GRNorth] VARCHAR(5), [County] INT, [District] VARCHAR(2), [Ward] VARCHAR(2), [UserType] BIT, [GridStatus] SMALLINT, [Country] SMALLINT, [WardStatus] SMALLINT, [NHSCode] VARCHAR(3), [NHSRegion] VARCHAR(3), [Longitude] DECIMAL(9,6), [Latitude] DECIMAL(9,6), [OSRef] VARCHAR(15), [Updated] DATE)
CREATE TABLE #Postcodes ([Postcode] VARCHAR(10), [IntDate] VARCHAR(10), [GREast] VARCHAR(255), [GRNorth] VARCHAR(255), [County] VARCHAR(255), [District] VARCHAR(255), [Ward] VARCHAR(255), [UserType] VARCHAR(255), [GridStatus] VARCHAR(255), [Country] VARCHAR(255), [WardStatus] VARCHAR(255), [NHSCode] VARCHAR(255), [NHSRegion] VARCHAR(255), [Longitude] varcHar(255), [Latitude] varchar(255), [OSRef] VARCHAR(17), [Updated] VARCHAR(255))
DECLARE @SQL VARCHAR(255)
SET @SQL = 'BULK INSERT #Postcodes FROM "c:\uk-post-codes-2009.csv" WITH (FIELDTERMINATOR= '','', ROWTERMINATOR='''+CHAR(10)+''', FIRSTROW=2)'
EXEC(@SQL)

INSERT INTO Postcodes ([Postcode], [IntDate], [GREast], [GRNorth], County, District, Ward, UserType, GridStatus, Country, WardStatus, NHSCode, NHSRegion, Longitude, Latitude, OSRef, Updated)
SELECT Replace([PostCode],'"',''), LEFT(REPLACE([IntDate],'"',''),6)+'01', REPLACE([GREast],'"',''), REPLACE([GRNorth],'"',''), REPLACE([County],'"',''), REPLACE([District],'"',''), REPLACE([Ward],'"',''), REPLACE([UserType],'"',''), REPLACE([GridStatus],'"',''), REPLACE([Country],'"',''), REPLACE([WardStatus],'"',''), REPLACE([NHSCode],'"',''), REPLACE([NHSRegion],'"',''), [Longitude], [Latitude], REPLACE([OSRef],'"',''), REPLACE([Updated],'"','') FROM #Postcodes
DROP TABLE #Postcodes

The reason I used EXEC on the BULK INSERT Command is because the file has Char(10) as a line terminator, and I couldn't seem to specify it as a ROWTERMINATOR option without stuffing it into a string.

After the script has finished you should have 1,841,117 rows, you'll need to add your own indexes.