Copy records of one year to another year

I have three tables, State, City, and Village.

State   (PK_Id,Name,Year)
City    (PK_Id,Name,FK_StateId)
Village (PK_Id,Name,Year,FK_CityId)

PK means primary key and FK is foreign key. I want to copy every state of last year to new year, and every city of last year, and every village of last year.

Is it possible? How can I do it? It is really harder than it seems.

Update: The problem appears when I want to copy cities of last ye开发者_Python百科ar to a new year. And it seems really impossible when I decide to copy villages of last year to new year.

---

INSERT INTO State (Name, Year)
SELECT S.Name, 2011
FROM State S
WHERE S.Year = 2010

INSERT INTO City (Name, FK_StateId)
SELECT C.Name, S_new.PK_Id
FROM City C
INNER JOIN State S_old ON C.FK_StateId = S_old.PK_Id
  AND S_old.Year = 2010
INNER JOIN State S_new ON S_old.Name = S_new.Name
  AND S_new.Year = 2011

INSERT INTO Village (Name, Year, FK_CityId)
SELECT V.Name, 2011, C_new.PK_Id
FROM Village V
INNER JOIN City C_old ON C.FK_CityId = C_old.PK_Id
  AND C_old.Year = 2010
INNER JOIN City C_new ON C_old.Name = C_new.Name
  AND C_new.Year = 2011

---

You can use temporary tables to store a copy of the 3 tables. Update the year in all temp tables and then insert back into main tables.