查询再插入

今天看到一位朋友的提问 转载参考下
一个SQL问题，以MySQL为例

#两张表：
#部门表department(department_id,department_name)
#人员表staff(staff_id,department_id,staff_no,staff_name)
#staff_backup(staff_id,department_id,staff_no,staff_name,Back_date)

问题：从staff表中找出所有department_name是dept_1的数据，插入staff_backup表中，并插入备份时间

注意：这里操作的是使用SQL

建表的语句：

drop database if exists depart;
create database depart;
use depart;

create table department(
department_id int(10) NOT NULL auto_increment,
department_name varchar(20),
PRIMARY KEY(department_id)
);

create table staff(
staff_id int(10) NOT NULL auto_increment,
department_id int(10),
staff_no varchar(20) ,
staff_name varchar(20),
PRIMARY KEY(staff_id)
);

create table staff_backup(
staff_id int(10) NOT NULL auto_increment,
department_id int(10),
staff_no varchar(20),
staff_name varchar(20),
back_date datetime,
PRIMARY KEY(staff_id)
);

#======================================
#初始化department表数据
insert into department values(null, 'dept_1');
insert into department values(null, 'dept_2');
insert into department values(null, 'dept_3');
insert into department values(null, 'dept_4');
insert into department values(null, 'dept_5');
#======================================

#======================================
#初始化staff表数据
insert into staff values(null, 1, '00001', 'Tom');
insert into staff values(null, 2, '00002', 'Jery');
insert into staff values(null, 3, '00003', 'Mike');
insert into staff values(null, 4, '00004', 'Lily');
insert into staff values(null, 5, '00005', 'Lucy');
insert into staff values(null, 1, '00006', 'Henry');
insert into staff values(null, 2, '00007', 'Tiger');
insert into staff values(null, 3, '00008', 'Scott');
insert into staff values(null, 4, '00009', 'Lary');
insert into staff values(null, 5, '00010', 'Linc');
#======================================

现在的问题是：从staff表中找出所有department_name是dept_1的数据是多条结果，使用SQL的嵌套查询貌似实现不了，谁能给个解决办法？

如果不使用存储过程是否能够实现？
采纳的答案
2008-11-22 jander (初级程序员)
insert into staff_backup
( staff_id,department_id,staff_no,staff_name ,back_date)
select staff_id,department_id,staff_no,staff_name ,now() from staff where department_name='dept_1'