Country State And City Table - Best Practices

Oct 2, 2007

Quick questiom, I need to Import a table with City and State Information

What are your recomendations? Should I create a Unique Key for them?
CityID, CityName, StateID?
StateID, StateName?

Or can I just store them in a table with one column?
CityName, State

Thanks for your help/

View 2 Replies


ADVERTISEMENT

Transact SQL :: Server Script For List Of All Country State And City

Aug 20, 2014

I am searching for the Sql Server Script of all country State and City with the Following type..

For the Country only 2 Columns are there i.e. CountryId and CountryName(CountryId is primary Key)

For the State 3 Columns are there i.e. CountryId, StateId and StateName(StateId is Primary Key and CountryId is Foreign Key)

For the City 3 Columns are there i.e. StateId ,CityId and CityName(CityId is PrimaryKey and StateId is foreign Key).

I need this type of Script with Column name.

View 7 Replies View Related

Selecting First 10 Addresses In Each City In Each State

Oct 4, 2007

Greetings:

I read with great interest a post on getting hte top 75 per cent by month, but still get through this problem. I have a table containing addresses, city names and state names, Each row has a unique address. I want to get the first ten addresses for each city in each state. I've tried something like the following which didn't work. Suggestions on how to solve the (simple) problem would be appreciated.
Select top (10) city,state,address
from address_list
alan

View 3 Replies View Related

Combine City Field With Prov/State

Jun 4, 2007

Hello I am new to SQL Server 2005 and am designing my first database.

In the AdventureWorks sample database the city field is included with the street address where it repeats for each row.



Why would you not put it in a separate table with state/province or a separate table all togeather?



Thanks for your help.

View 5 Replies View Related

The OLE DB Provider MSDAORA For Linked Server .... Does Not Contain The Table COUNTRY. The Table Either Does Not Exist Or The Current User Does Not Have Permissions On That Table.

Jun 13, 2006

I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
 
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.

View 3 Replies View Related

Best Practices: Table Definition Updates

Feb 5, 2008



Hi. We're using SQL Server 2005 Express Edition for maintaining a relational DB for our soon-to-be released .NET app. The problem is that we expect that the definitions of the tables in the DB to occasionally change over time as we make updates to the software. Thus, during the installation of a software upgrade, we expect to run an SQL script that grabs the data from a table in its "old" format, re-structures the table, and then deposits the data back in the updated table. This seems to require some sort of version stamp on the table definition.

My main question is: What is the conventional way for handling versioning of table definitions?

Another question is: Is there a preferred procedure for handling the updating of the data during the installation of a software update?

Thanks.

Dave

View 3 Replies View Related

List A City Only Once

Nov 13, 2006

Hi...I want one listbox showing cities but I dont want to list a city more than one time....

I know that DISTINCT maybe could work...But I dont get it to work correctly....

The code:

<asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True" DataSourceID="DataSource1" DataTextField="City" DataValueField="City"></asp:ListBox>

<asp:SqlDataSource ID="DataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT [City] FROM [Location]">

</asp:SqlDataSource>

I got the message:

The text data type cannot be selected as DISTINCT because it is not comparable

 The database have a table called "Location" with the column "ID" (int) as primary key and the column "City" (text)

View 14 Replies View Related

This Is Bat Country

Apr 2, 2008

Hello,
I'm new to the SQL world and want to know if this is the site for a newbie or would you recommend another site more my speed. Thanks for the input.

Clint




US Navy - fueled, armed, and go for launch.

View 7 Replies View Related

Transact SQL :: Convert City Name To Upper / Lower

Jul 7, 2015

In the database, most of our cities are stored in all upper case.  For reporting purposes, I need to have them returned as upper/lower.   I used the below function, which works great for one word cities.   However I can’t figure out how to get it to capitalize the 1st letter of each word, for addresses containing multiple names such as Rancho Santa Margarita. 

Upper(left(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY,1))+lower(substring(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY, 2, LEN(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY)))As ADDRESSCITY

This returns back: ‘Rancho santa margarita’; I need it to return ‘Rancho Santa Margarita’.  Is this possible to do at the query level?

View 10 Replies View Related

Continents To Country

Mar 2, 2007

I have the country iso table does anyone have the data for tying contenents to the countries?


You can do anything at www.zombo.com

View 5 Replies View Related

Query For Ip And Associated Country

Dec 28, 2007

Hi,

I have created some statistics for my site based on the ip of the visitors. For that reason I have created two tables
1. One table with a column which hold the ip number of the visitor
Table: AccessLog
Column: IP

2. One table with the ip range and the countries
Table: Countries
Columns: ip_to, ip_from, country_name

I would like to create a query which will bring the country name of the visitor IP which IP will be like "ip_to <= ip <= ip_from"

Thanks

Denis

www.tabletennis.gr

View 20 Replies View Related

State Table Script

Aug 12, 2002

Anyone have a script to create a table of US States?
I'd rather not type in all 50 states if possible :)

Thanks!
The forums search page returned no such script :(

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

View 5 Replies View Related

Add A State To Table That Is Already Created?

Feb 16, 2015

I have this table that I thought I could just do a normal update to because I need to add a state and ID.

How do I add a state to a table that is already created?

Here is the behind the scenes. I have never had to deal with something like this.

This is what it looks like

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RegionStates](
[RegionID] [smallint] NOT NULL,

[code]....

View 1 Replies View Related

Store Procedure To Find City And Zip Code By Radius

Mar 20, 2007

Hi,
 
Is anyone has store procedure that find city and zipcode by radius (10 miles, 30 miles, 50 miles.......)
and also table that has all zipcodes. I finded one, but it's look like very old one, not updated since 2005
 
Thanks
Kate
 
 

View 1 Replies View Related

Selecting Nearby Distinct City/zipcode Issue

Mar 12, 2004

I have the following code, which returns a list of nearby cities based on a city name as input. Most cities have multiple zipcodes per city name, thus it can list multiple rows with the same city name, but with different zipcodes like below:

Zip | Cityname
111 belmont
112 belmont
113 belmont
114 san francisco
115 san francisco

---------------- etc----------------

I do not really care about each group of zipcodes. I only need one pair of zipcode/city name like the following:

ZIP | City name
111 belmont
114 San Francisco


How do I change my select to only return a distinct city name. I do not care which if the city/zipcodes it returns from the similar city.

The select statement is below:




CREATE PROCEDURE ZipSearchByCity
@city varchar(40),
@State varchar(5),
@distance int


AS


SELECT distinct o.City AS City, o.zip_code, o.State AS State,
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) dist

FROM zipcodes z,
zipcodes o,
zipcodes a




WHERE z.city = @city AND
z.State = @State AND
z.zip_code=a.zip_code AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) < @distance
GO





Thanks,

Christian

View 7 Replies View Related

SQL 2012 :: Fetch Only Country Name

Nov 18, 2014

I dont know the exact position of '/',',','--' or any special character.I have to fetch first name of the country.

create table abc
(country nvarchar(200))
go
INSERT INTO abc VALUES ('Tennessee/Virginia,USA')
GO
INSERT INTO abc VALUES ('Kansas,USA')

[code]...

View 7 Replies View Related

Know The Country Version Of SQL Server

Jul 20, 2005

Hi!How I can know the "Country version" of SQL Server using VB.NET(perhaps with the SQLDMO)?I mean: if the SQL Server is in "English version" or in "Italianversion" or ...Thank you so much!!Bye

View 1 Replies View Related

Need To Produce A Query That Show The Cities And The Number Of People Per City?

Jan 31, 2008



Please help!!!!
Need to produce a query that show the cities and the number of people per city?
simple table
firstname
lastname
age
location

location shows the dif cities and the sql query will show cities will a count of people per city

how do you do this?

View 3 Replies View Related

Insert Country Code Into Columns

Nov 9, 2011

I am having a users table which contains "Mobile" column as well. I want a query to set the country code value by default into the column name so that the column should be updated with the mobile number along with the default country code.

View 10 Replies View Related

T-SQL (SS2K8) :: Returning Rows Within Certain Distance In Miles From City Using Longitude And Latitude

Nov 19, 2014

I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.

Two things I don't like about query:

1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results.
2. very slow to retrieve results (> 1 minute)

Sample DDL: 2 tables
create table dim_lead
(
date_created datetime,
[contact_first_name] varchar(20),
[contact_last_name] varchar(20),
lead_id int,

[Code] .....

View 9 Replies View Related

Moving Db Across The Country- Full Copy, Then Differential?

Jul 23, 2005

My company needs to move a 30 Gig SQL Server across the country as soonas possble on July 1. Turns out moving the full db across the networktakes a few hours.I'd like to move a full copy of db a week ahead of time, and then justmove either a differential backup(s) or transaction log with with theweek's new data on July 1.Can anyone suggest the best strategy for doing this? Currently we'redoing a differential backup each hour on the db, and dumping the txnlog each night.Many thanks,Burt

View 1 Replies View Related

Get Invalid Cursor State When Modifying A Table In SQL 2000

Jul 3, 2007

When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state.

SP3 has been applied to SQL Server 2000.

Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.

View 2 Replies View Related

Select Unique Country / Date Pairs With Self Join

Sep 16, 2013

I have built a sample table, query, and results for this question. I am using SQL server.

declare @TableX Table
(
Date Date not null,
ID int not null,
Tick varchar(6) not null
)
INSERT @tableX

[Code] ....

Expected results:
DATE(No column name)count
2013-09-02LON1
2013-09-03LON1
2013-09-04LON1
2013-09-05LON1
2013-09-06LON1
2013-09-02USA2
2013-09-03USA2
2013-09-04USA2
2013-09-05USA2
2013-09-06USA2

I want to select unique country - date pairs. It is not even necessary to have the count of each one, just the list of unique country/dates.

My query here uses 'group by' to accomplish this task, but there may be a way to do this with a self join. I believe using a self join would make the query faster.

1) Is this possible to do with a self join?

View 2 Replies View Related

Retrieve Data Form A Country Combined With 3 To 4 Cities

Feb 12, 2015

This Question is pertaining to AND and OR operators.

If we want to retrieve data form a country combined with 3 to 4 cities how do we handle this?

Say for ex:- i want to retrieve all data from Customers table where country is Germany and cities are Berlin, Mannheim,Brandenburg and München.

View 1 Replies View Related

Get Percentage With Variation Of Field Values (country Names)

Sep 14, 2006

Any help here would be greatly appreciated...

Unfortunately, data wasn't filtered prior to getting inserted into this table. Now I am stuck with cleaning it up. I have thought about writing a query to update all the values, but there are just too many variations, including spelling mistakes, so I've ruled that out as a possible solution.

I have a table which has a Country field but the values per record vary. For example US, U.S., USA, United States, UK, United Kingdom, Canada, Can, etc. I'm trying to find the percent of records per country.

Sample table data: mytable
Id  Name  Country
1  John     US
2  James  UK
3  Jane     United States
4  Mary    Canada
5  Jack     U.S.
6  Tony     United Kingdom
7  Jeff       US
8  Tom     Canada
9  Beth     UK
10  Mark   USA

I would like to show
US: 50%   --> (includes any variation of US ncluding US, U.S., USA, United States)
UK: 30%
CAN: 20%

I've made several attempts myself with no luck. Thanks in advance.

View 3 Replies View Related

Help!!!! Restore File Mdf After Exec Drop Table... To Previous State.

Aug 8, 2006

Hi all.
First, sorry about my poor english.

I have a database which above 6 gb data and i have droped all table in my database.
I try to restore from log file but my database is set to simple backup and auto shrink so log file doesn't help anymore.
I have used some software to recovery from log file too but useless.
My only hope right now is mdf file.
Please help me. How could i restore my mdf file to state before i droped tables.
Thanks

View 14 Replies View Related

SQL Server Session State - Using A 1.1 Database Schema For 2.0 Session State Storage

Aug 3, 2006

The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.

We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).

Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?

Thanks.

View 1 Replies View Related

Principal Db In Principal, Synchronizing State, The Mirror In Restoring State

Sep 26, 2007

We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.

This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.

Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?

Thanks in advance.

View 3 Replies View Related

Stored Procedure To Get All Users Or All Users Of A Specific Country

Apr 30, 2008

hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){    WHERE fk_country = @fk_country}
who has an idea how to solve this problem?

View 9 Replies View Related

QN Best Practices

Sep 18, 2007

Hi everyone,

I wonder if somebody here could recommend a good article about MS Service Broker. I'm looking for some advice and tips in designing applications using SQL Service Broker, mainly QN. For instance, maintenance routines and common faulty scenarios I might find later when my solution is implemented. I have googled for a while but all I can find are recopied examples of QN.

Thanks

View 3 Replies View Related

SqlDataSource Best Practices

Jun 20, 2008

Hi I use data presentation controls like gridview, formsview in my application. In many of the webforms i also use multiple datasources mainly for the purpose of 2 way data binding for controls within data presentation controls.I am concerned about the performance issues this might cause as users using these pages increase.What is the likely performance impact ?Once the databind is done and values are populated in the respective controls, does the database connection of datasource control get closed, or is it open?What are the best practices while implementing datasource controls? 

View 2 Replies View Related

SQL 2000 Best Practices

Nov 16, 2001

Hi all. Thanks for taking the time to read this.

I'm looking for some documentation on SQL 2K Installation tips on a Windows 2000 Member Server platform as well as best practices for ongoing maintenance .

Real world experience as well as Microsoft propaganda are all welcome.

Thanks again.

View 1 Replies View Related

Best Practices ( Installation )

Dec 7, 2001

Could some one give best SQL 2000 install practices such as -
1) SQL app, should this be in OS drive or not ( pros, cons )
2) Should OS be on Raid 1

etc

Thanks,

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved