I wrote an application for resident housing at a college. In one of the tables (rooms) I have a list of all the rooms and their current/max occupancy. Now, I've added a new column called "semester" and set all of the existing rows to have a semester value of "fall." Now I want copy and paste all of these rows into the table but change the semester value to "spring." The result should be twice as many rows as I started with - half with fall in the semester value and half with spring. Wondering what the best way to accomplish this is?
Add a comment
|
4 Answers
INSERT INTO rooms
(roomname, current_occupancy, max_occupancy, semester)
SELECT roomname, current_occupancy, max_occupancy,'spring'
FROM rooms
WHERE [semester]='fall'
(assuming names for your room and occupancy columns)
Comments
Use a temp table to make it simple no matter how many columns are involved;
SELECT * INTO #ROOMS FROM ROOMS;
UPDATE #ROOMS SET SEMESTER='spring';
INSERT INTO ROOMS SELECT * FROM #ROOMS;
answered Mar 12, 2012 at 18:28
Joachim Isaksson
182k28 gold badges298 silver badges308 bronze badges
Comments
Insert Into Rooms
Select col1, col2, col3, 'Spring' as Semester -- select each column in order except 'Semester', pass it in literally as 'Spring'
From rooms where
Semester = 'Fall'
Comments
Well if you're just trying to do this inside Sql Server Management Studio you could copy the table, run an Update command and set the semester to spring on the cloned table, then use the wizard to append the data from the cloned table to the existing table.
If you know a programming language you could pull all of the data, modify the semester, then insert the data into the existing table.
Note: The other answers are a much better way of achieving this.
