openGauss数据导入教程-优快云博客

本文链接：https://blog.youkuaiyun.com/hezuijiudexiaobai/article/details/122015568

本文详细介绍使用openGauss数据库进行数据导入的各种方法，包括直接插入数据、表间数据迁移、表合并及通过文件导入导出数据等实用技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

0.导入数据方案

1️⃣ insert into
2️⃣ meger into
3️⃣ copy

1.创建表1并在表中插入数据，分别指定字段和整行为缺省值

CREATE TABLE reason_t1
(
    r_reason_sk integer,
    r_reason_id character(16),
    r_reason_desc character(100)
);
\d+ reason_t1

--普通插入数据
insert into reason_t1 values(1, 'AAAAAAAABAAAAAAA', 'reason1');
select * from reason_t1;

--没有数值的字段将被填充为字段的缺省值
insert into reason_t1 values(2, 'AAAAAAAABAAAAAAA');
select * from reason_t1;

--明确字段为缺省值
insert into reason_t1 values(2, 'AAAAAAAABAAAAAAA', DEFAULT);
select * from reason_t1;

--明确整行为缺省值
insert into reason_t1 DEFAULT VALUES;
select * from reason_t1;

2.创建表2并将表1的数据全部导入表2中

CREATE TABLE reason_t2
(
    r_reason_sk integer,
    r_reason_id character(16),
    r_reason_desc character(100)
);

create table reson_t2_test as select * from reason_t1 where 1=2;
\d+ reson_t2_test

INSERT INTO reason_t2 SELECT * FROM reason_t1;
select * from reason_t2;

3.创建表3和表4，并合并两个表的数据到表3

CREATE TABLE reason_t3
( 
    product_id INTEGER,
    product_name VARCHAR2(60),
    category VARCHAR2(60)
);
INSERT INTO reason_t3 VALUES
(1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');

CREATE TABLE reason_t4
( 
    product_id INTEGER,
    product_name VARCHAR2(60),
    category VARCHAR2(60)
);
INSERT INTO reason_t4 VALUES
(1501, 'vivitar 35mm', 'electrncs'),
(1502, 'olympus ', 'electrncs'),
(1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'dvd');

MERGE INTO reason_t3 r3
USING reason_t4 r4
ON (r3.product_id = r4.product_id )
WHEN MATCHED THEN
UPDATE SET r3.product_name = r4.product_name, r3.category = r4.category
WHEN NOT MATCHED THEN
INSERT VALUES (r4.product_id, r4.product_name, r4.category) ;

select * from reason_t3 order by product_id;

4.将表3的数据输出到文件，再将文件中的数据导入到表5

copy reason_t3 to stdout;
copy reason_t3 to '/home/omm/reason3.dat';

CREATE TABLE reason_t5 (LIKE reason_t3);
\d+ reason_t5

copy reason_t5 from '/home/omm/reason3.dat';
select * from reason_t5;