Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。点 击 Getting Started with Amazon AWS to see specific differences applicable to the China (Beijing) Region.

INSERT 示例

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

Copy
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 表,但为列定义默认值:

Copy
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 表。

Copy
insert into category_stage (select * from category);

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

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

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

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

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

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

Copy
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 值,并为这两行中的其他列插入默认值。

Copy
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)

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

Copy
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 插入到新表:

Copy
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 语句中将子查询用作表达式:

Copy
insert into category(catid) values ((select max(catid)+1 from category)), ((select max(catid)+2 from category)); ERROR: cannot use subqueries in multi-row VALUES