Copy and Paste Rows into Same SQL Table with Different Values

Question

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?

Solution 1

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)

Solution 2

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;

Solution 3

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'

Solution 4

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.

View more solutions