数据库怎样把一个表的数据插入到另一个表

发布时间：2012-12-26 10:19:38 来源：文档文库

小中大

字号：

手机查看

MySQL 数据库怎样把一个表的数据插入到另一个表

2010-08-23 11:19:56

标签：mysql insert 另一张表

原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。http://yangfei520.blog.51cto.com/1041581/381568

web开发中，我们经常需要将一个表的数据插入到另外一个表，有时还需要指定导入字段，设置只需要导入目标表中不存在的记录，虽然这些都可以在程序中拆分成简单sql来实现，但是用一个sql的话，会节省大量代码。下面我以mysql数据库为例分情况一一说明：

两张表：insertTest和insertTest2，前者中有测试数据

create table insertTest(id int(4),name varchar(12));

insert into insertTest values(100,'liudehua');

insert into insertTest values(101,'zhourunfa');

insert into insertTest values(102,'zhouhuajian');

1.如果2张表的字段一致，并且希望插入全部数据，可以用这种方法：

INSERT INTO 目标表 SELECT * FROM 来源表;

insert into insertTest select * from insertTest2;

2.如果只希望导入指定字段，可以用这种方法：

INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;

注意字段的顺序必须一致。

insert into insertTest2(id) select id from insertTest2;

3.如果您需要只导入目标表中不存在的记录，可以使用这种方法：

INSERT INTO 目标表

(字段1, 字段2, ...)

SELECT 字段1, 字段2, ...

FROM 来源表

WHERE not exists (select * from 目标表

where 目标表.比较字段 = 来源表.比较字段);

1>.插入多条记录：

insert into insertTest2

(id,name)

select id,name

from insertTest

where not exists (select * from insertTest2

where insertTest2.id=insertTest.id);

2>.插入一条记录：

insert into insertTest

(id, name)

SELECT 100, 'liudehua'

FROM dual

WHERE not exists (select * from insertTest

where insertTest.id = 100);

使用 dual 作表名，select 语句后面直接跟上要插入的字段的值。

本文出自 “夜狼” 博客，请务必保留此出处http://yangfei520.blog.51cto.com/1041581/381568

一个利用触发器（trigger）记录表操作的例子

2007-12-21 16:54

首先建立数据库Test，建立两个表，我们命名为grades和audit，利用audit实现对grades的审计功能，其中grades用于存放学生的成绩，包括“sdudentID，courseID，grade”三个字段，主键为sdudentID，courseID；audit仅仅比表grades增加了两个字段：changeType，changTime，用于记录grades表被修改的类型和修改时间。

建立2个表格的sql语句如下：

create table grades(

studentID int,

courseID int,

grade int,

primary key(studentID,courseID)

);

create table audit(

changeType char(15),

changeTime datetime,

studentID int,

courseID int,

grade int,

primary key(changeType,changeTime,studentID,courseID,grade)

)

然后在表grades上建立触发器，如果对grades表进行“insert”，"delete","update"操作，在audit表中分别记录这些操作的类型（update(old,new)，insert或者delete），操作的时间，操作影响的数据（比如被删除的数据，插入的数据，修改前的数据，修改后的数据）。

建立触发器的sql语言如下：

/*the beginning of the trigger definition*/

create trigger tr_GradesChanged on grades for delete,insert,update

as

declare @insertedCount int

declare @deletedCount int

declare @changeType char(10)

declare @changeTime datetime

declare @updateType char(4)

/*在ms sql server中有两个临时表保存着被删除和被插入的记录，分别叫“deleted”，“inserted”。update可以看作一次删除和一次添加*/

select @insertedCount=count(*) from inserted

select @deletedCount=count(*) from deleted

select @changeType=

case

when @insertedCount>0 and @deletedCount>0

then 'update'

when @insertedCount=0 and @deletedCount>0

then 'delete'

else 'insert'

end

select @changeTime=getdate() //select语句为变量赋值！

select @updateType=''

if @changeType='update' select @updateType=' old'

insert into audit(changeType,changeTime,studentID,courseID,grade) select @changeType+@updateType, @changeTime,studentID,courseID,grade from deleted

if @changeType='update' select @updateType=' new'

insert into audit(changeType,changeTime,studentID,courseID,grade) select @changeType+@updateType, @changeTime,studentID,courseID,grade from inserted

/*the end of the trigger definition*/

至此，整个触发器就建立好了，我们可以对此触发器进行测试

运行以下语句：

delete from grades

delete from audit

insert into grades values (19224,002,90)

update grades set grade=100 where studentID =19224

delete from grades where studentID=19224

select * from audit order by changeTime asc

将得到以下结果：

可见，audit表对grades表的操作进行了监控，记载了其变化情况。由于上面的几条测试语句运行的时间间隔太短，所以changetime相同，查询结果首先按changtime升序排列，然后按changtype升序（默认）排列，所以和实际结果有点差别，理论上应该是：

changType changeTime studentID courseID grade

1 insert ----------- 19224 2 90

2 update old ----------- 19224 2 90

3 update new ----------- 19224 2 100

4 delete ---------- 19224 2 100