We help IT Professionals succeed at work.

SQL Server how to select an id from one table and insert into another when related

Hi Experts,

In SQL Server 2008 I have 3 tables:
1. State (fields = ID,statename)
2. County (fields = ID,stateid,countyname)
3. Town (fields = ID,stateid,countyid,townname)

Every field except stateid in the table called Town is populated. I know I can relate tables however for reasons too long to go into, I need to have the relevant stateid in the Town table so my question is:

How do I make a query using SQL Server where it inserts the correct stateid into the Town table? (presumably by connecting the last two tables (County and Town) on County field ID = Town field countyid)

It must be an efficient piece of code, my Town table has nearly 120,000 rows in it! I *MUST* do it this way, not relate the tables.

Thanks

/ Tobzzz

Comment
Watch Question

Senior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
UPDATE T
SET stateID = C.stateid
FROM Town T
INNER JOIN County C ON
    T.countyid = C.ID
Top Expert 2011

Commented:
My code would look like this:

Insert into Town (stateId)

select stateId from State where stateID not in (select stateId from Town)

Author

Commented:
Perfect - didn't realise it was so easy! Well, it's easy...when you know how! Many thanks.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.