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 selectfrom）要求目标表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 ascity_id,
    cityid ascounty_id,
    '2011-08-1500:00:00' as BEGIN_TIME,
    '2011-08-1500:00:00'as END_TIME,
    cityname asCOUNTY_NAME,
    1 asACTIVE_FLAG,
    1 asAREA_CODE,
    '地区' asDESC_TXT
FROM
   user_city
WHERE
    parentidLIKE '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 ascounty_id,
   t1.cityname asdept_name,
   1 as ACTIVE_FLAG,
   '2011-08-15 00:00:00' asBEGIN_TIME,
    '2011-08-1500:00:00'as END_TIME,
   '片区' as DESC_TXT
FROM
    (select *from user_city where parentid like 'city101_') t1,(select * fromuser_city where parentid like 'city1_') t2

WHERE t1.parentid =t2.cityid;