excel import all cities and state in SQL server table

Manoj Maharana

10y1.1k

I have 2 tables City_Locality and Country_State:

In Country_State table consisting of ID,Name,ParentID,HasState,Currency,IsActive,IsTopCountry.

In City_Locality table consisting of

ID,StateID,CountryID,Name,ParentID,DisplayOrder.IsActive,IsRequest,IsTopCity.

I have a excel sheet(city.xlsx) having city_name,city_state.

I want to import data from excel sheet to database by using sql 2008. I have already state data is in the Country_State table. I have already some city data is in the City_Locality table.

I want a sql query to check duplicacy while insert the data in City_Locality table and what is the sql query to insert excel data into database??

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manoj Maharana10y ago
Vinay Singh can you write down the query clearly...
0
Nitin Sontakke10y ago
Is it possible / appropriate for you to upload city.xlsx here? So that a possible formula can be demonstrated.
You have more columns in target table City_Locality as compare to city.xlsx, what data should go in rest of the columns and also mention which Excel column maps to which table column, for clarity.
Duplicate to be checked on the basis of which column?
Putting quick Excel formulas is an absolutely essential skill for any developer, so learn it quickly. It is very simple.
0
Vinay Singh10y ago
Hi insted of putting formula in excel use the sql query which full fill you req.
duplicate check...
if not existed (select * from City_Locality where Stateid='@youestateid' and Country='@yourcountryid')
Begin
----Insert query to save data inCity_Locality
End
For retreving the data ..
select * From City_Locality cl
inner join Country_State cs in cl.stateid=cs.stateid
0
Manoj Maharana10y ago
how to putthe formula in Excel??
0
Nitin Sontakke10y ago
If it is a one time job, i suggest you put the formula in Excel and generate a query. Copy those cells and paste it in Query Analyzer window and execute it.
0