将行复制并粘贴到具有不同值的同一个SQL表中

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 fall. Wondering what the best way to accomplish this is?

我写了一份大学住宿申请。在其中一张桌子(房间)中，我列出了所有房间的清单，以及它们当前/最大入住率。现在，我添加了一个名为“学期”的新专栏，并将所有现有的行设置为“秋季”的学期值。现在我想把所有这些行复制粘贴到表中，但是把学期值改为“spring”。结果应该是我开始时的两倍——一半是学期价值的下降，一半是秋天。想知道最好的方法是什么吗?

4 个解决方案

#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)

(假设你的房间和占用栏的名称)

#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;

#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'

#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.

如果你只是想在Sql Server Management Studio中这样做，你可以复制这个表，运行一个更新命令，并在克隆的表上设置这个学期的spring，然后使用向导将克隆的表中的数据附加到现有的表中。

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.

注意:其他答案是实现这一目标的更好方法。

#1

INSERT INTO rooms
(roomname, current_occupancy, max_occupancy, semester)
SELECT roomname, current_occupancy, max_occupancy,'spring'
FROM rooms
WHERE [semester]='fall'