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

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\操作，在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