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 ...