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
 General SQL Server Forums
 New to SQL Server Programming
 filling table using INSERT geting strange error		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

statix
Starting Member

12 Posts
Posted - 02/09/2013 :  14:00:27  Show Profile  Reply with Quote
whats wrong with my query ?
Thanks in advance!


INSERT INTO SCCZ
                  ( state_id, county_id, city_id, zipcode_id)
SELECT state.state_id, county.county_id, city.city_id, zipcode.zipcode_id
FROM     state county city zipcode

James K
Flowing Fount of Yak Knowledge

1483 Posts
Posted - 02/09/2013 :  16:00:58  Show Profile  Reply with Quote
When you want to compose results from more than one table, you need to JOIN those tables. A good explanation here: http://www.w3schools.com/sql/sql_join.asp

So you will need to do something to join the state, county, city and zipcode tables. What are the columns in these tables? Are there additional columns that let you relate a county to a state or a city to a county etc.? If you can post the DDL for the tables, that would help a lot. See here on how to post DDLs: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

statix
Starting Member

12 Posts
Posted - 02/09/2013 :  16:11:37  Show Profile  Reply with Quote 

SELECT DISTINCT x1.state_id, x2.county_id, x3.city_id, x4.zipcode_id
FROM     state AS x1 INNER JOIN
                  datazip AS d ON x1.state_name = d.state INNER JOIN
                  county AS x2 ON d.county = x2.county_name INNER JOIN
                  city AS x3 ON d.city = x3.city_name INNER JOIN
                  zipcode AS x4 ON d.zipcode = x4.zipcode_name


like this?
if yes then how i put the data into table that already exist by same order after doing this query..?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1483 Posts
Posted - 02/09/2013 :  16:18:29  Show Profile  Reply with Quote
If the select statement gives you the correct data, then inserting is easy - add the insert statement like shown in red below:
INSERT INTO SCCZ
                  ( state_id, county_id, city_id, zipcode_id)
SELECT DISTINCT x1.state_id, x2.county_id, x3.city_id, x4.zipcode_id
FROM     state AS x1 INNER JOIN
                  datazip AS d ON x1.state_name = d.state INNER JOIN
                  county AS x2 ON d.county = x2.county_name INNER JOIN
                  city AS x3 ON d.city = x3.city_name INNER JOIN
                  zipcode AS x4 ON d.zipcode = x4.zipcode_name
Go to Top of Page

statix
Starting Member

12 Posts
Posted - 02/10/2013 :  10:11:40  Show Profile  Reply with Quote
THanks! works like a charm! :P
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1483 Posts
Posted - 02/10/2013 :  11:27:51  Show Profile  Reply with Quote
You are very welcome - glad to help.
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.08 seconds. Powered By: Snitz Forums 2000