INSERT INTO SELECT 语句及使用
SQL里怎么把一个数据库的表复制到另一个数据库?
1.
SELECT * INTO 表1 FROM 表2 --复制表2
如果只复制结构而不复制内容或只复制某一列
只要加WHERE条件就好
2.
假设你有数据库A和数据库B,现将数据库A中的Table_A的数据复制到数据库B中的Table_B,语句
1如果Table_A和Table_B的结构一样:
use B
insert into Table_B select * from A.dbo.Table_A
2如果Table_A和Table_B的结构一样:
insert into Table_B(B字段1B字段2...) select A字段1A字段2... from A.dbo.Table_A
上面的两个实例用到了select into from 和 insert into select ,那么这两个语法有什么不同哪?
select into from 和 insert into select都是用来复制表,两者的主要区别为: select into from 要求目标表不存在,因为在插入时会自动创建。insert into select from 要求目标表存在
备份表数据: create table emp as select from scott.emp
还原表数据:insert into emp select from scott.emp
- 复制表结构及其数据:
create table table_name_new as select * from table_name_old
- 只复制表结构:
create table table_name_new as select * from table_name_old where 1=2;
或者:
create table table_name_new like table_name_old
- 只复制表数据:
如果两个表结构一样:
insert into table_name_new select * from table_name_old
如果两个表结构不一样:
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old
pasting
再次对使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了,进行说明
1.INSERT INTO SELECT语句
语句形式为:Insert into Table2(field1,field2,...) select value1,value2,... from Table1 [where column =value]
[]为可选内容
要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量。示例如下:
insert into tjjrmx(yybh,xh,tjxmbh,jg,sfyx,zhxmbh,tjksbh,jcrq,jcys,ts,ckfw,disporder)
select '24',xh,tjxmbh,jg,sfyx,zhxmbh,tjksbh,jcrq,jcys,ts,ckfw,disporder from tjjrmx where yybh = 5
2.SELECT INTO FROM语句
语句形式为:SELECT vale1, value2 into Table2 from Table1
要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中。示例如下
-------------------------------------------------------------------------------------------------------------------
实战例1
如何在mysql从多个表中组合字段然后插入到一个新表中(特别是在多个表的数据集中到一个表时),通过一条sql语句实现。具体情形是:有三张表a、b、c,现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段。对于这种情况,我们可以使用如下的语句来实现:
1. INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name
当然,上面的语句比较适合两个表的数据互插,如果多个表就不适应了。对于多个表,我们可以先将需要查询的字段join起来,然后组成一个视图后再select from就可以了:
2.1 INSERT INTO a(field1,field2) SELECT FROM(SELECT f1,f2 FROM b JOIN c) AS tb
其中f1是表b的字段,f2是表c的字段,通过join查询就将分别来自表b和表c的字段进行了组合,然后再通过select嵌套查询插入到表a中,这样就满足了我们这个场景了,如果需要不止2个表,那么可以多个join的形式来组合字段。需要注意的是嵌套查询部分最后一定要有设置表别名,如下:
2.2 SELECT FROM(SELECT f1,f2 FROM b JOIN c) AS tb
即最后的as tb是必须的(当然tb这个名称可以随意取),即指定一个别名,否则在mysql中会报如下错误:
2. 3 ERROR 1248 (42000): Every derived TABLE must have its own alias
即每个派生出来的新表都必须指定别名才可以的。
***********************************************************************************************************
实战例2
insert触发器 insert into select同时插入多条记录
需求:写了一个FOR INSERT的触发器,每次插入数据的时候向相关的数据表中也同时插入数据。
问题:测试的时候,一次只向主表中插入一条记录,那么触发器没有问题。在实际使用的时候,因为使用了insert into select语句,一次向主表中插入了超过一条的记录,如17条,但是发现其他数据表中却都只有一条记录,明显出现了错误。
解决:
1、上网查询“一次插入多条”这样的关键字
2、通过在触发器中书写:select count(*) from inserted,的确可以看到一次插入了多条
3、网上说用“游标”来实现
分析:
1、其实不用右边也可以,如果只是把插入的数据直接插入其他相关的数据表,完全可以使用insert into (select from inserted)来实现。
例如:
CREATE TRIGGER TriInsertStoreFile ON tblStore
FOR INSERT
AS
BEGIN
Insert into storeFile(storeID) (SELECT storeID FROM INSERTED)
END
2、而我在实际应用中,还存在与storeFile关联的storeFileItem数据表,要用插入storeFile时自动生成的主键FileID,插入storeFileItem中作为外键关联,看来只有用"游标"了,其实也不难
CREATE TRIGGER TriInsertStoreFile ON tblStore
FOR INSERT
AS
DECLARE @storeID int
DECLARE @FILEID int
SET @storeID=0
SET @FILEID=0
DECLARE stores_cursor CURSOR FOR
SELECT storeID FROM Inserted
OPEN stores_cursor
FETCH NEXT FROM stores_cursor
INTO @storeID
WHILE @@FETCH_STATUS = 0
BEGIN
Insert into storeFile(storeID) (SELECT storeID FROM INSERTED)
SET @FILEID=@@identity
Insert into storeFileItem(fileID) values(@FILEID)
FETCH NEXT FROM stores_cursor INTO @storeID
END
CLOSE stores_cursor
DEALLOCATE stores_cursor
总算解决问题。
最后能把对其他数据表的插入写在单独的存储过程中,就更理想了
sqlserver在触发器中执行动态sql用到inserted(deleted)表
这两天公司产品中要加个数据同步用的触发器,oracle版没什么问题,很顺利的写完了。
在写sqlserver版时遇到了麻烦。由于在触发器中要根据inserted(deleted)表的数据,对另一个表进行更新操作,而这个表的某些字段是不固定的,所以需要写成动态sql,利用exec来执行。这时麻烦来了,exec执行时,无法访问到inserted(deleted)表。坏了,这该如何是好?
经反复google+baidu+试验,问题总算解决了。总结一下,大致有以下两种方法:
1、将inserted(deleted)表的数据放到临时表#temp,然后再来exec;
2、用游标对inserted(deleted)表进行逐条处理。
具体采用哪种,可根据业务特点来定
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341