GeoNames to Location Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

GeoNames to Location Table

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2014-02-01 : 14:12:20

I have the following:
<code>
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[itweb_GeoNames](
[GeonameID] [int] NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[AnsiName] [varchar](200) NULL,
[AlternateNames] [nvarchar](max) NULL,
[Latitude] [float] NOT NULL,
[Longitude] [float] NOT NULL,
[FeatureClass] [char](1) NULL,
[FeatureCode] [varchar](10) NULL,
[CountryCode] [char](2) NULL,
[CC2] [varchar](60) NULL,
[Admin1Code] [varchar](20) NULL,
[Admin2Code] [varchar](80) NULL,
[Admin3Code] [varchar](20) NULL,
[Admin4Code] [varchar](20) NULL,
[Population] [bigint] NOT NULL,
[Elevation] [int] NULL,
[GTopo30] [int] NULL,
[Timezone] [varchar](50) NULL,
[ModificationDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

I would like to
extract the data into the following format
even if there are duplicate cities

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Location](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Country] [nvarchar](max) NOT NULL,
[City] [nvarchar](max) NOT NULL,
[State] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

</code>

Any help would be awesome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-02 : 11:48:23

Can you explain rules for converting data to your output? Cant relate source to destination table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources