logo - 跳到首页

VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

SELECT INTO

功能描述

Vastbase G100在SQL Server兼容模式下支持SELECT INTO语法，该语法从一个表中选取数据，然后把数据插入另一个表中；也就是将SELECT在源表的查询结果用于创建一个新表。

注意事项

  • 本特性仅在数据库兼容模式为SQL Server时能够使用（即创建DB时DBCOMPATIBILITY='MSSQL'），在其他数据库兼容模式下不能使用该特性。

  • 源表可以是普通表（单表），分区表，本地临时表或全局临时表；新表可以为普通表，也可以是本地临时表或全局临时表。

  • 新表中的列顺序和行顺序与SELECT所指的中间结果集一致。

  • 源数据如果来自分区表，新表仍然为单表。

  • 新表只复制源数据的列信息，如类型、宽度、精度。不会将源表中的索引、约束、触发器复制到新表上。

  • 当SELECT语句指定的选择数据进行ORDER BY子句强排序时，新表的数据排列按照SELECT查询时所指定的顺序，参考示例2。

  • 当SELECT指定的列存在函数计算时，新表插入的数据是函数计算的结果，参考示例3。

  • 支持使用#table_name和##table_name变量表示临时表（参考示例4）：

    • #开头的名字表示本地临时表。

    • ##开头的名字表示全局临时表。

  • 支持跨schema访问，即源表和新表可以来自不同的schema。

  • 目前仅支持本地使用，不支持在远程数据库中创建新表。

语法格式

SELECT column_name [, ...] 
INTO new_table
FROM old_table
[WHERE condition]
ORDER BY expression [ASC|DESC];

参数说明

  • column_name

    源表中的列名，也可以是含列名的表达式。

  • new_table

    要创建的新表的名字。名称前可加上模式名，如：schema_name.table_name。

  • old_table

    用于提供数据的源表名。表名前可加上模式名，如：schema_name.table_name。

  • [WHERE condition]

    WHERE子句构成一个行选择表达式，用来缩小SELECT查询的范围。

  • expression

    ORDER BY表达式，对SELECT语句检索得到的数据进行排序。

  • [ ASC | DESC ]

    查询结果的排序方式是升序（ASC）还是降序（DESC），默认是升序排列。

示例

前置条件

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建并切换至兼容模式为SQL Server的数据库下。

create database my_test with dbcompatibility='MSSQL';
\c my_test

示例1： 将SELECT的查询结果用于创建一个新表。

1、创建测试表作为源表。

CREATE TABLE tasks_1142523 (
task_id INT NOT NULL PRIMARY KEY,
subject VARCHAR(45) NULL,
start_date DATE NULL,
end_date DATE NULL,
description VARCHAR(200) NULL
);

2、插入测试数据。

insert into tasks_1142523 values(1, 'Chinese1', '2022-02-01', '2023-03-01', 'this is line1');
insert into tasks_1142523 values(2, 'Chinese2', '2022-02-01', '2023-03-01', 'this is line2');
insert into tasks_1142523 values(3, 'Chinese3', '2022-02-01', '2023-03-01', 'this is line3');
insert into tasks_1142523 values(4, 'Chinese4', '2022-02-01', '2023-03-01', 'this is line4');
insert into tasks_1142523 values(5, 'Chinese5', '2022-02-01', '2023-03-01', 'this is line5');

3、使用SELECT INTO语法，将SELECT的查询结果用于创建一个新表。（有两条符合查询条件的数据被插入新表。）

select task_id, subject, description into new_table_1142523_01 from tasks_1142523 where task_id > 3;

4、查看新表的表结构。

\d+ new_table_1142523_01;

返回结果如下，新表和源表数据结构一致：

                      Table "public.new_table_1142523_01"
   Column    |     Type     | Modifiers | Storage  | Stats target | Description
-------------+--------------+-----------+----------+--------------+-------------
 task_id     | integer      |           | plain    |              |
 subject     | varchar(45)  |           | extended |              |
 description | varchar(200) |           | extended |              |
Has OIDs: no
Options: orientation=row, compression=no, fillfactor=80

5、向新表插入一条数据。

insert into new_table_1142523_01 values(6, 'Chinese6', 'this is line6');

6、查看新表的数据。

select * from new_table_1142523_01;

返回结果如下，包含从源表选取的部分数据和上一步插入的数据。

 task_id | subject  |  description
---------+----------+---------------
       4 | Chinese4 | this is line4
       5 | Chinese5 | this is line5
       6 | Chinese6 | this is line6
(3 rows)

示例2： SELECT查询数据时进行ORDER BY排序。

1、创建测试表作为源表。

CREATE TABLE tasks_1142716 (
task_id INT NOT NULL PRIMARY KEY,
test_id INTEGER check(test_id > 0),
subject VARCHAR(45) NOT NULL,
start_date DATE NULL default '2022-01-01',
end_date DATE NOT NULL,
description VARCHAR(200) UNIQUE
);

2、插入数据。

insert into tasks_1142716 values(1, 1, 'Chinese1', '2022-02-01', '2023-03-01', 'this is line1');
insert into tasks_1142716 values(4, 4, 'Chinese4', '2022-02-01', '2023-03-01', 'this is line4');
insert into tasks_1142716 values(5, 5, 'Chinese5', '2022-02-01', '2023-03-01', 'this is line5');
insert into tasks_1142716 values(2, 2, 'Chinese2', '2022-02-01', '2023-03-01', 'this is line2');
insert into tasks_1142716 values(3, 3, 'Chinese3', '2022-02-01', '2023-03-01', 'this is line3');

3、将SELECT的查询结果用于创建一个新表，按照test_id降序排列。

select * into new_table_1142716_01 from tasks_1142716 where task_id >= 2 order by test_id desc;

4、查看新表的数据。

select * from new_table_1142716_01;

返回结果如下，新表数据按照test_id降序排列：

 task_id | test_id | subject  | start_date |  end_date  |  description
---------+---------+----------+------------+------------+---------------
       5 |       5 | Chinese5 | 2022-02-01 | 2023-03-01 | this is line5
       4 |       4 | Chinese4 | 2022-02-01 | 2023-03-01 | this is line4
       3 |       3 | Chinese3 | 2022-02-01 | 2023-03-01 | this is line3
       2 |       2 | Chinese2 | 2022-02-01 | 2023-03-01 | this is line2
(4 rows)

示例3： SELECT查询指定的列中存在函数计算。

1、创建测试表作为源表。

CREATE TABLE tasks_1142715 (
task_id INT NOT NULL PRIMARY KEY,
test_id INTEGER check(test_id > 0),
subject VARCHAR(45),
start_date DATE NULL default '2022-01-01',
end_date DATE NOT NULL,
description VARCHAR(200) UNIQUE
);

2、插入测试数据。

insert into tasks_1142715 values(1, 1, 'Chinese1', '2022-02-01', '2023-03-01', 'this is line1');
insert into tasks_1142715 values(2, 2, NULL, '2022-02-01', '2023-03-01', 'this is line2');
insert into tasks_1142715 values(3, 3, 'Chinese3', '2022-02-01', '2023-03-01', 'this is line3');
insert into tasks_1142715 values(4, 4, NULL, '2022-02-01', '2023-03-01', 'this is line4');
insert into tasks_1142715 values(5, 5, 'Chinese5', '2022-02-01', '2023-03-01', 'this is line5');

3、将SELECT的查询结果用于创建一个新表，SELECT查询的列中存在函数计算。

select task_id  as c1, test_id as c2, ISNULL(NULL, subject) as c3, LEN(description) as c4, sysdatetime() as c5, CHARINDEX(subject, description) as c6 into new_table_1142715 from tasks_1142715 where task_id > 2;

4、查看新表的数据。

select * from new_table_1142715;

返回结果如下，插入的数据是函数计算的结果。

 c1 | c2 |    c3    | c4 |             c5             | c6
----+----+----------+----+----------------------------+----
  3 |  3 | Chinese3 | 13 | 2023-05-24 22:58:23.863486 |  0
  4 |  4 |          | 13 | 2023-05-24 22:58:23.863486 |
  5 |  5 | Chinese5 | 13 | 2023-05-24 22:58:23.863486 |  0
(3 rows)

示例4： 在使用SELECT的查询结果创建新表时使用#table_name和##table_name变量表示临时表。

1、创建测试表作为源表。

CREATE TABLE tasks_1142827 (
task_id INT NOT NULL PRIMARY KEY,
test_id INTEGER check(test_id > 0),
subject VARCHAR(45),
start_date DATE NULL default '2022-01-01',
end_date DATE NOT NULL,
description VARCHAR(200) UNIQUE
);

2、插入数据。

insert into tasks_1142827 values(1, 1, 'Chinese1', '2022-02-01', '2023-03-01', 'this is line1');
insert into tasks_1142827 values(2, 2, NULL, '2022-02-01', '2023-03-01', 'this is line2');
insert into tasks_1142827 values(3, 3, 'Chinese3', '2022-02-01', '2023-03-01', 'this is line3');
insert into tasks_1142827 values(4, 4, NULL, '2022-02-01', '2023-03-01', 'this is line4');
insert into tasks_1142827 values(5, 5, 'Chinese5', '2022-02-01', '2023-03-01', 'this is line5');

3、查询源表的数据并插入新表，使用#table_name变量指定新表为本地临时表。

select task_id as c1, test_id as c2, ISNULL(NULL, subject) as c3, LEN(description) as c4, sysdatetime() as c5, CHARINDEX(subject, description) as c6 into #new_table_1142827_01 from tasks_1142827 where task_id > 2;

4、查询源表的数据并插入另一张新表，使用##table_name变量指定新表为全局临时表。

select task_id as c1, test_id as c2, ISNULL(NULL, subject) as c3, LEN(description) as c4, sysdatetime() as c5, CHARINDEX(subject, description) as c6 into ##new_table_1142827_02 from tasks_1142827 where task_id > 2;

5、查询两张新表的数据插入结果：

select * from new_table_1142827_01;
select * from new_table_1142827_02;

返回结果如下，查询到新创建的临时表数据：

 c1 | c2 |    c3    | c4 |             c5             | c6
----+----+----------+----+----------------------------+----
  3 |  3 | Chinese3 | 13 | 2023-05-24 23:10:42.574776 |  0
  4 |  4 |          | 13 | 2023-05-24 23:10:42.574776 |
  5 |  5 | Chinese5 | 13 | 2023-05-24 23:10:42.574776 |  0
(3 rows)

 c1 | c2 |    c3    | c4 |             c5             | c6
----+----+----------+----+----------------------------+----
  3 |  3 | Chinese3 | 13 | 2023-05-24 23:10:48.639236 |  0
  4 |  4 |          | 13 | 2023-05-24 23:10:48.639236 |
  5 |  5 | Chinese5 | 13 | 2023-05-24 23:10:48.639236 |  0
(3 rows)

6、退出当前的客户端seeison。

\q

7、重新连接至my_test数据库，开始新session。

vsql -d my_test -p 5432 -r

8、再次查看两张新表的数据：

  • 使用#table_name变量创建的本地临时表：

    select * from new_table_1142827_01;

    返回结果如下，本地临时表在新session中不可见。

    ERROR:  relation "new_table_1142827_01" does not exist on node1
LINE 1: select * from new_table_1142827_01;
                    ^

  • 使用##table_name变量创建的全局临时表：

    select * from new_table_1142827_02;

    返回结果如下，全局临时表在新session中可见，但是无数据。

    c1 | c2 | c3 | c4 | c5 | c6
----+----+----+----+----+----
(0 rows)