时间戳

首页 > 解决方案 > 为什么我的“INSERT INTO”语句不起作用？

sql - 为什么我的“INSERT INTO”语句不起作用？

问题描述

我正在使用 Apache derbyDB (db2) 和 SQL Workbench 作为环境。我有 10k 条数据记录，我想创建一个包含维度表和事实表的星型模式。

dwh_price_paid_records = data table
dm_price_paid_records = data mart table (facts table)
dim_time =  data mart table (dimension)

DWH_PRICE_PAID_RECORDS.TRANSACTION_ID = VARCHAR(50)
DWH_PRICE_PAID_RECORDS.PRICE = INTEGER
DWH_PRICE_PAID_RECORDS.DATE_OF_TRANSFER = DATE
DIM_TIME.DATE_ID = Primary key of dim_time (autoincrement start with 1 increment by 1)
DM_PRICE_PAID_RECORDS.DATE_ID = Foreign key NOT NULL from DIM_TIME

在 Facts 表 (DM_PRICE_PAID_RECORDS) 中，我只想拥有主键、价格（事实）和 ID，以便与维度表建立关系，但它不起作用。

INSERT INTO DM_PRICE_PAID_RECORDS (TRANSACTION_ID, PRICE, DATE_ID) VALUES (SELECT TRANSACTION_ID FROM DWH_PRICE_PAID_RECORDS, SELECT PRICE FROM DWH_PRICE_PAID_RECORDS, SELECT DATE_ID FROM DIM_TIME join DWH_PRICE_PAID_RECORDS on DIM_TIME.DATE = DWH_PRICE_PAID_RECORDS.DATE_OF_TRANSFER WHERE DIM_TIME.DATE = 
DWH_PRICE_PAID_RECORDS.DATE_OF_TRANSFER);

我本来希望这是一个结果：

TRANSACTION_ID | 价格 | DATE_ID

1| 第2324章 423

2 | 52315 | 234

但是我在德语中遇到错误，例如：“VALUES-clause must have atleast one element. Empty elements are not allowed”

“价值观-Klausel muss mindestens ein Element enthalten。Leere Elemente sind nicht zulässig。”

太感谢了！菲皮

标签： sqlderby

解决方案

我想你想要INSERT . . . SELECT：

INSERT INTO DM_PRICE_PAID_RECORDS (TRANSACTION_ID, PRICE, DATE_ID)
    SELECT ppr.TRANSACTION_ID, ppr.PRICE, t.DATE_ID
    FROM DWH_PRICE_PAID_RECORDS ppr JOIN
        DIM_TIME t 
        ON t.DATE = ppr.DATE_OF_TRANSFER ;

推荐阅读