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 _ STAGE 表與表類似的CATEGORY模式，但定義列的默認值：

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)

下面的例子顯示了一些多行INSERTVALUES語句。第一個範例會為兩列插入特定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陳述式指定DEFAULTVALUES的結果相同。

下列範例顯示資料表包含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;

請注意，您不能在列中插入特定的整數CATIDIDENTITY值。IDENTITY欄值會自動產生。

下面的例子演示了子查詢不能用作多行INSERTVALUES語句中的表達式：

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