SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 GeoNames to Location Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mcupryk
Yak Posting Veteran

Canada
90 Posts

Posted - 02/01/2014 :  14:12:20  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 02/02/2014 :  11:48:23  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000