select into from和insert into select from两种表复制语句区别

select * into target_table from source_table;

insert into target_table(column1,column2) select column1,5 from source_table; 

 

以上两句都是将源表source_table的记录插入到目标表target_table,但两句又有区别。
第一句(select into from)要求目标表target_table不存在,因为在插入时会自动创建。
第二句(insert into select from)要求目标表target_table存在,由于目标表已经存在,所以我们除了插入源表source_table的字段外,还可以插入常量,如例中的:5。

 

例如:需要将user_city表中的部分字段值拷贝到dim_pub_county_act表中(字段名不同),则语句如下:

INSERT
INTO
    SUI1.DIM_PUB_COUNTY_ACT
    (
        CITY_ID,
        COUNTY_ID,
        BEGIN_TIME,
        END_TIME,
        COUNTY_NAME,
        ACTIVE_FLAG,
        AREA_CODE,
        DESC_TXT
    )
SELECT
    parentid as city_id,
    cityid as county_id,
    '2011-08-15 00:00:00' as BEGIN_TIME,
    '2011-08-15 00:00:00'as END_TIME,
    cityname as COUNTY_NAME,
    1 as ACTIVE_FLAG,
    1 as AREA_CODE,
    '地区' as DESC_TXT
FROM
    user_city
WHERE
    parentid LIKE 'city1_';

 

-----------------------------------------------------------------

 

INSERT
INTO
    SUI1.DIM_PUB_DEPT_ACT
    (
        DEPT_ID,
        CITY_ID,
        COUNTY_ID,
        DEPT_NAME,
        ACTIVE_FLAG,
        BEGIN_TIME,
        END_TIME,
        DESC_TXT
    )
  SELECT
   t1.cityid as dept_id,
   t2.parentid as city_id,
   t1.parentid as county_id,
   t1.cityname as dept_name,
   1 as ACTIVE_FLAG,
   '2011-08-15 00:00:00' as BEGIN_TIME,
    '2011-08-15 00:00:00'as END_TIME,
   '片区' as DESC_TXT
FROM
    (select * from user_city where parentid like 'city101_') t1,(select * from user_city where parentid like 'city1_') t2

WHERE t1.parentid = t2.cityid;

posted @ 2017-07-14 18:29  Mr.石  阅读(2187)  评论(0编辑  收藏  举报