Scripts Microsoft SQL para criar estrutura e importação de tabelas
Regiões Administrativas: criar tabela Copy
CREATE TABLE Admin_Regions (
ISO VARCHAR(2) NOT NULL,
Country NVARCHAR(45) NOT NULL,
Language VARCHAR(2) NOT NULL,
Level INT NOT NULL,
Category NVARCHAR(45) NOT NULL,
CategoryEN NVARCHAR(45) NOT NULL,
Region1Code VARCHAR(10) NOT NULL,
Region1 NVARCHAR(85) NOT NULL,
Region2Code VARCHAR(10),
Region2 NVARCHAR(85),
Region3Code VARCHAR(10),
Region3 NVARCHAR(85),
Region4Code VARCHAR(10),
Region4 NVARCHAR(85),
Latitude decimal(10, 6),
Longitude decimal(10, 6),
Elevation Int,
ISO2 VARCHAR(10),
FIPS VARCHAR(10),
NUTS VARCHAR(30),
HASC VARCHAR(12),
STAT VARCHAR(20),
CONSTRAINT PK_Admin_Regions PRIMARY KEY CLUSTERED
(
ISO ASC,
Language ASC,
Region1Code ASC,
Region2Code ASC,
Region3Code ASC,
Region4Code ASC
) ON [PRIMARY]
);
Importar arquivo: Copy
BULK INSERT Admin_Regions FROM 'Path\Geo-XX-4-XXX.DAT' WITH (FIRSTROW = 2, FIELDTERMINATOR = ';', DATAFILETYPE = 'widechar')
Códigos postais: criar tabela Copy
CREATE TABLE Postal_Codes (
ISO varchar(2) NOT NULL,
Country nvarchar(45) NOT NULL,
Language varchar(2) NOT NULL,
ID bigint NOT NULL,
PostalCode varchar(10),
Region1 nvarchar(85),
Region2 nvarchar(85),
Region3 nvarchar(85),
Region4 nvarchar(85),
Locality nvarchar(110),
Suburb nvarchar(110),
Latitude decimal(10,6),
Longitude decimal(10,6),
Elevation Int,
TimeZone varchar(35),
UTC varchar(6),
DST varchar(6),
CONSTRAINT PK_Postal_Codes PRIMARY KEY CLUSTERED (ISO ASC, Language ASC, ID ASC) ON [PRIMARY]
);
Importar arquivo: Copy
BULK INSERT Postal_Codes FROM 'Path\Geo-XX-5-XXX.DAT' WITH (FIRSTROW = 2,FIELDTERMINATOR = ';', DATAFILETYPE = 'widechar')
Polígonos de códigos postais: criar tabela Copy
CREATE TABLE Boundaries (
ISO varchar(2) NOT NULL,
Layer int NOT NULL,
Code varchar(12) NOT NULL,
Name nvarchar(85) NOT NULL,
Geometry geography NOT NULL,
CONSTRAINT PK_Boundaries PRIMARY KEY CLUSTERED (ISO ASC, Layer ASC, Code ASC) ON [PRIMARY]
);
Importar arquivo - SQL: Copy
BULK INSERT Boundaries FROM 'path\Geo-XX-7-XXX.DAT' WITH (DATAFILETYPE='widechar')
Importar arquivo - BCP Utility: Copy
About Microsoft BCP (bulk copy program utility).
bcp [database].dbo.[Boundaries] in "path\Geo-XX-7-XXX.DAT" -T -c
Ruas: criar tabela Copy
CREATE TABLE Streets (
ISO varchar(2) NOT NULL,
Country nvarchar(45) NOT NULL,
Language varchar(2) NOT NULL,
ID bigint NOT NULL,
PostalCode varchar(10),
Region1 nvarchar(85),
Region2 nvarchar(85),
Region3 nvarchar(85),
Region4 nvarchar(85),
Locality nvarchar(110),
Suburb nvarchar(110),
Street nvarchar(110),
Range varchar(100),
Building nvarchar(85),
Latitude decimal(10,6),
Longitude decimal(10,6),
Elevation Int,
TimeZone varchar(35),
UTC varchar(6),
DST varchar(6),
CONSTRAINT PK_Streets PRIMARY KEY CLUSTERED (ISO ASC, Language ASC, ID ASC) ON [PRIMARY]
);
Importar arquivo: Copy
BULK INSERT Streets FROM 'Path\Geo-XX-8-XXX.DAT' WITH (FIRSTROW = 2, FIELDTERMINATOR = ';', DATAFILETYPE = 'widechar')
