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