INSERT 示例 - Amazon Redshift

INSERT 示例

TICKIT 数据库中的 CATEGORY 表包含以下行：

 catid | catgroup |  catname  |                  catdesc
-------+----------+-----------+--------------------------------------------
     1 | Sports   | MLB       | Major League Baseball
     2 | Sports   | NHL       | National Hockey League
     3 | Sports   | NFL       | National Football League
     4 | Sports   | NBA       | National Basketball Association
     5 | Sports   | MLS       | Major League Soccer
     6 | Shows    | Musicals  | Musical theatre
     7 | Shows    | Plays     | All non-musical theatre
     8 | Shows    | Opera     | All opera and light opera
     9 | Concerts | Pop       | All rock and pop music concerts
    10 | Concerts | Jazz      | All jazz singers and bands
    11 | Concerts | Classical | All symphony, concerto, and choir concerts
(11 rows)

使用与 CATEGORY 表类似的 schema 来创建 CATEGORY_STAGE 表，但为列定义默认值：

create table category_stage
(catid smallint default 0,
catgroup varchar(10) default 'General',
catname varchar(10) default 'General',
catdesc varchar(50) default 'General');

下面的 INSERT 语句从 CATEGORY 表中选择所有行并将它们插入 CATEGORY_STAGE 表。

insert into category_stage
(select * from category);

查询两旁的括号是可选的。

此命令在 CATEGORY_STAGE 表中插入新行，并按顺序为每列指定值：

insert into category_stage values
(12, 'Concerts', 'Comedy', 'All stand-up comedy performances');

您还可以插入结合使用特定值和默认值的新行：

insert into category_stage values
(13, 'Concerts', 'Other', default);

运行以下查询以返回插入的行：

select * from category_stage
where catid in(12,13) order by 1;

 catid | catgroup | catname |             catdesc
-------+----------+---------+----------------------------------
    12 | Concerts | Comedy  | All stand-up comedy performances
    13 | Concerts | Other   | General
(2 rows)

下面的示例说明了一些多行 INSERT VALUES 语句。第一个示例为两行插入特定的 CATID 值，并为这两行中的其他列插入默认值。

insert into category_stage values
(14, default, default, default),
(15, default, default, default);

select * from category_stage where catid in(14,15) order by 1;
 catid | catgroup | catname | catdesc
-------+----------+---------+---------
    14 | General  | General | General
    15 | General  | General | General
(2 rows)

下一个示例插入包含特定值和默认值的各种组合的三行：

insert into category_stage values
(default, default, default, default),
(20, default, 'Country', default),
(21, 'Concerts', 'Rock', default);

select * from category_stage where catid in(0,20,21) order by 1;
 catid | catgroup | catname | catdesc
-------+----------+---------+---------
     0 | General  | General | General
    20 | General  | Country | General
    21 | Concerts | Rock    | General
(3 rows)

本示例中的第一组 VALUES 生成的结果与为单行 INSERT 语句指定 DEFAULT VALUES 所生成的结果相同。

以下示例说明当表具有 IDENTITY 列时的 INSERT 行为。首先，创建 CATEGORY 表的新版本，然后将行从 CATEGORY 插入到新表：

create table category_ident
(catid int identity not null,
catgroup varchar(10) default 'General',
catname varchar(10) default 'General',
catdesc varchar(50) default 'General');


insert into category_ident(catgroup,catname,catdesc)
select catgroup,catname,catdesc from category;

请注意，您不能将特定的整数值插入到 CATID IDENTITY 列。IDENTITY 列值会自动生成。

以下示例说明了不能在多行 INSERT VALUES 语句中将子查询用作表达式：

insert into category(catid) values
((select max(catid)+1 from category)),
((select max(catid)+2 from category));

ERROR: can't use subqueries in multi-row VALUES

以下示例显示了在临时表中插入的内容，临时表中使用 WITH SELECT 子句填充了 venue 表中的数据。有关 venue 表的更多信息，请参阅 示例数据库。

首先，创建临时表 #venuetemp。

CREATE TABLE #venuetemp AS SELECT * FROM venue;

列出 #venuetemp 表中的行。

SELECT * FROM #venuetemp ORDER BY venueid;
         
venueid | venuename                | venuecity  | venuestate| venueseats
--------+--------------------------+------------+-----------+------------
1        Toyota Park                Bridgeview   IL          0	
2        Columbus Crew Stadium      Columbus     OH          0	
3        RFK Stadium                Washington   DC          0	
4        CommunityAmerica Ballpark  Kansas City  KS          0	
5        Gillette Stadium           Foxborough   MA          68756	
...

使用 WITH SELECT 子句在 #venuetemp 表中插入 10 个重复的行。

INSERT INTO #venuetemp (WITH venuecopy AS (SELECT * FROM venue) SELECT * FROM venuecopy ORDER BY 1 LIMIT 10);

列出 #venuetemp 表中的行。

SELECT * FROM #venuetemp ORDER BY venueid;
         
venueid | venuename                | venuecity  | venuestate| venueseats
--------+--------------------------+------------+-----------+------------
1        Toyota Park                Bridgeview   IL          0	
1        Toyota Park                Bridgeview   IL          0	
2        Columbus Crew Stadium      Columbus     OH          0	
2        Columbus Crew Stadium      Columbus     OH          0	
3        RFK Stadium                Washington   DC          0
3        RFK Stadium                Washington   DC          0	
4        CommunityAmerica Ballpark  Kansas City  KS          0	
4        CommunityAmerica Ballpark  Kansas City  KS          0	
5        Gillette Stadium           Foxborough   MA          68756
5        Gillette Stadium           Foxborough   MA          68756
...