I inserted a temp column which consisted of the old ids. It created that and made the new lessons with the new school year. I was able to insert the new assignments with the new years, but when I referred back to the lesson table of the column oldId, it did not update to the new ids. I used your code (Borislav Borissov)
ALTER TABLE Lesson ADD OldId int DEFAULT(0)
GO
UPDATE Lessons Set OldId = Less_Id WHERE schlyr = 2008
INSERT INTO Lessons (lesson_title, lesson_desc, inst, schlyr, OldId)
SELECT (lesson_title, lesson_desc, inst, 2009, OldId)
FROM Lessons WHERE schlyr = 2008
INSERT INTO ASSIGNMENTS
(assign_title, assign_desc, assign_less_id, schlyr)
SELECT assign_title, assign_desc, Lessons.less_id , 2009
FROM ASSIGNMENTS
INNER JOIN Lessons ON ASSIGNMENTS.assign_less_id = Lessons.OldId AND Lessons.schlyr = 2009
WHERE ASSIGNMENTS.schlyr = 2008
ALTER TABLE Lesson DROP COLUMN OldId int DEFAULT(0)
ALTER TABLE Lesson ADD OldId int DEFAULT(0)
GO
UPDATE Lessons Set OldId = Less_Id WHERE schlyr = 2008
INSERT INTO Lessons (lesson_title, lesson_desc, inst, schlyr, OldId)
SELECT (lesson_title, lesson_desc, inst, 2009, OldId)
FROM Lessons WHERE schlyr = 2008
INSERT INTO ASSIGNMENTS
(assign_title, assign_desc, assign_less_id, schlyr)
SELECT assign_title, assign_desc, Lessons.less_id , 2009
FROM ASSIGNMENTS
INNER JOIN Lessons ON ASSIGNMENTS.assign_less_id = Lessons.OldId AND Lessons.schlyr = 2009
WHERE ASSIGNMENTS.schlyr = 2008
ALTER TABLE Lesson DROP COLUMN OldId int DEFAULT(0)