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
 General SQL Server Forums
 New to SQL Server Programming
 filling table using INSERT geting strange error

Author  Topic 

statix
Starting Member

12 Posts

Posted - 2013-02-09 : 14:00:27
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-09 : 16:00:58
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 - 2013-02-09 : 16:11:37
[code]
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[/code]

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-09 : 16:18:29
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 - 2013-02-10 : 10:11:40
THanks! works like a charm! :P

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-10 : 11:27:51
You are very welcome - glad to help.
Go to Top of Page
   

- Advertisement -