Category: SQL Server

[codesyntax lang=”sql”]

DROP TABLE #employee;

DROP TABLE #employee1;

CREATE TABLE #employee (

employee_id integer,

first_name  VARCHAR(20),

last_name   VARCHAR(20),

dept_no     integer,

salary      integer);

INSERT INTO #employee VALUES (1, 'Dan', 'Morgan', 10, 100000);

INSERT INTO #employee VALUES (2, 'Jack', 'Cline', 20, 100000);

INSERT INTO #employee VALUES (3, 'Elizabeth', 'Scott', 20, 50000);

INSERT INTO #employee VALUES (4, 'Jackie', 'Stough', 20, 40000);

INSERT INTO #employee VALUES (5, 'Richard', 'Foote', 20, 30000);

INSERT INTO #employee VALUES (6, 'Joe', 'Johnson', 20, 70000);

INSERT INTO #employee VALUES (7, 'Clark', 'Urling', 20, 90000);

SELECT * FROM #employee;

CREATE TABLE #employee1 (

employee_id integer,

first_name  VARCHAR(20),

last_name   VARCHAR(20),

dept_no     integer,

salary      integer);

INSERT INTO #employee1 VALUES (1, 'Dan', 'Morgan', 10, 100001);

INSERT INTO #employee1 VALUES (2, 'Jack', 'Cline', 20, 100000);

INSERT INTO #employee1 VALUES (3, 'Elizabeth', 'Scott', 20, 50002);

INSERT INTO #employee1 VALUES (4, 'Jackie', 'Stough', 24, 40000);

INSERT INTO #employee1 VALUES (5, 'Richard', 'Foote', 20, 30003);

INSERT INTO #employee1 VALUES (6, 'Joe', 'Johnson', 20, 70004);

INSERT INTO #employee1 VALUES (97, 'Clark', 'Urling', 23, 90000);

SELECT * FROM #employee1;

MERGE INTO #employee E

USING (

  SELECT *

  FROM #employee1) E1

ON (E.employee_id = E1.employee_id)

WHEN MATCHED THEN

  UPDATE SET E.dept_no = E1.dept_no, E.salary = E1.salary

WHEN NOT MATCHED THEN

  INSERT VALUES (E1.employee_id,E1.first_name,E1.last_name,E1.dept_no,E1.salary);

  SELECT * FROM #employee;

  SELECT * FROM #employee1;

[/codesyntax]

A few post have been made with complex solutions for importing information from GeoNames.org into SQL Server.  These involved downloading converters to change the file from UTF-8 encoding and downloading additional software (EditPad Pro) to complete the conversion to UTF-16.

A much simpler way is to open the txt file in Microsoft Excel. Let Excel perform it’s magic on the tab delimited file.

Add the headers to the top row:

The main 'geoname' table has the following fields :
---------------------------------------------------
geonameid         : integer id of record in geonames database
name              : name of geographical point (utf8) varchar(200)
asciiname         : name of geographical point in plain ascii characters, varchar(200)
alternatenames    : alternatenames, comma separated varchar(5000)
latitude          : latitude in decimal degrees (wgs84)
longitude         : longitude in decimal degrees (wgs84)
feature class     : see http://www.geonames.org/export/codes.html, char(1)
feature code      : see http://www.geonames.org/export/codes.html, varchar(10)
country code      : ISO-3166 2-letter country code, 2 characters
cc2               : alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters
admin1 code       : fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)
admin2 code       : code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80)
admin3 code       : code for third level administrative division, varchar(20)
admin4 code       : code for fourth level administrative division, varchar(20)
population        : bigint (4 byte int)
elevation         : in meters, integer
gtopo30           : average elevation of 30'x30' (ca 900mx900m) area in meters, integer
timezone          : the timezone id (see file timeZone.txt)
modification date : date of last modification in yyyy-MM-dd format

Then save the Excel file. Use SQL Server import to import the Excel file and your done.