SQL 在 Firebird 中插入选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21115720/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT SELECT in Firebird
提问by number5
I'm new to firebird and I have verious issues. I want to insert various lines into a table selected from another table.
我是 firebird 的新手，我有很多问题。我想将各种行插入从另一个表中选择的表中。
Here's the code:
这是代码：
/*CREATE GENERATOR POS; */
SET GENERATOR POS TO 1;
SET TERM ^;
create trigger BAS_pkassign
for MATERIAL
active before insert position 66
EXECUTE BLOCK
AS
declare posid bigint;
select gen_id(POS, 1)
from RDB$DATABASE
into :posid;
BEGIN
END
SET TERM ; ^
INSERT INTO MATERIAL ( /*ID */ LOCATION, POSID, ARTID, ARTIDCONT, QUANTITY )
SELECT 1000, ':posid', 309, BAS_ART.ID, 1
FROM BAS_ART
WHERE BAS_ART.ARTCATEGORY LIKE '%MyWord%'
The ID should autoincrement from 66 on. The posid should autoincrement from 1 on.
ID 应该从 66 开始自动递增。posid 应该从 1 开始自动递增。
Actually it is not inserting anything.
实际上它没有插入任何东西。
I'm using Firebird Maestro and have just opened the SQL Script Editor (which doesnt throw any error message on executing the script).
我正在使用 Firebird Maestro 并且刚刚打开了 SQL 脚本编辑器（它在执行脚本时不会抛出任何错误消息）。
Can anybody help me?
有谁能够帮助我？
Thanks!
谢谢！
Additional information:
附加信息：
The trigger should autoincrement the column "ID" - but I dont know how exactly I can change it so it works.. The ':posid' throws an error using it :posid but like this theres no error (I guess its interpretated as a string). But how do I use it right?
触发器应该自动增加“ID”列 - 但我不知道我如何确切地改变它以便它起作用..':posid'使用它抛出一个错误:posid但像这样没有错误（我猜它被解释为细绳）。但是我该如何正确使用它呢？
I dont get errors when I execute it. The table structure is easy. I have 2 tables: 1.
当我执行它时，我没有收到错误。表结构很简单。我有 2 张桌子：1。
Material (
ID (INTEGER),
Location (INTEGER),
POSID (INTEGER),
ARTID (INTEGER),
ARTIDCONT (INTEGER),
QUANTITY (INTEGER),
OTHERCOLUMN (INTEGER))
and the 2. other table
和 2. 其他表
BAS_ART (ID (INTEGER), ARTCATEGORY (VARCHAR255))
-> I want to insert all entries from the table BAS_ART which contain "MyWord" in the column ARTCATEGORY into the MATERIAL table.
-> 我想将表 BAS_ART 中的所有条目插入到 MATERIAL 表中，这些条目在列 ARTCATEGORY 中包含“MyWord”。
回答by a_horse_with_no_name
I don't understand why you need the trigger at all.
我不明白你为什么需要触发器。
This problem:
这个问题：
I want to insert all entries from the table BAS_ART which contain "MyWord" into the MATERIAL table
我想将表 BAS_ART 中包含“MyWord”的所有条目插入到 MATERIAL 表中
Can be solved with a single insert ... selectstatement.
可以用一个insert ... select语句解决。
insert into material (id, location, posid, artid, quantity)
select next value for seq_mat_id, 1000, next value for seq_pos, id, 1
from bas_art
where artcategory = 'My Word';
This assumes that there is a second sequence (aka "generator") that is named seq_mat_idthat provides the new id for the column material.id
这假设有第二个序列（又名“生成器”）被命名seq_mat_id为列提供新的 idmaterial.id
回答by Mark Rotteveel
For most of my answer I will assume a very simple table:
对于我的大部分答案，我将假设一个非常简单的表格：
CREATE TABLE MyTable (
ID BIGINT PRIMARY KEY,
SomeValue VARCHAR(255),
posid INTEGER
)
Auto-increment identifier
自增标识符
Firebird (up to version 2.5) does not have an identity column type (this will be added in Firebird 3), instead you need to use a sequence (aka generator) and a trigger to get this.
Firebird（直到 2.5 版）没有标识列类型（这将在 Firebird 3 中添加），您需要使用序列（又名生成器）和触发器来获取它。
Sequence
序列
First you need to create a sequence using CREATE SEQUENCE:
首先，您需要使用CREATE SEQUENCE以下命令创建一个序列：
CREATE SEQUENCE seqMyTable
A sequence is atomic which means interleaving transactions/connections will not get duplicate values, it is also outside transaction control, which means that a ROLLBACKwill not revert to the previous value. In most uses a sequences should always increase, so the value reset you do at the start of your question is wrong for almost all purposes; for example another connection could reset the sequence as well midway in your execution leaving you with unintended duplicates of POSID.
序列是原子的，这意味着交错事务/连接不会得到重复的值，它也在事务控制之外，这意味着 aROLLBACK不会恢复到以前的值。在大多数用途中，序列应该始终增加，因此您在问题开始时所做的值重置几乎对所有目的都是错误的；例如，另一个连接可能会在您执行的中途重置序列，让您意外重复POSID.
Trigger
扳机
To generate a value for an auto-increment identifier, you need to use a BEFORE INSERT TRIGGERthat assigns a generated value to the - in this example - IDcolumn.
要为自动递增标识符BEFORE INSERT TRIGGER生成值，您需要使用将生成的值分配给 - 在此示例中 -ID列。
CREATE TRIGGER trgMyTableAutoIncrement FOR MyTable
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.ID = NEXT VALUE FOR seqMyTable;
END
In this example I alwaysassign a generated value, other examplesassign a generated value only when the IDis NULL.
在这个例子中，我总是分配一个生成的值，其他例子只在IDis时分配一个生成的值NULL。
Getting the value
获取价值
To get the generated value you can use the RETURNING-clauseof the INSERT-statement:
为了得到产生价值，你可以使用RETURNING-clause中的INSERT语句来：
INSERT INTO MyTable (SomeValue) VALUES ('abc') RETURNING ID
INSERT INTO ... SELECT
插入...选择
Using INSERT INTO ... SELECTyou can select rows from one table and insert them into others. The reason it doesn't work for you is because you are trying to assign the string value ':pos'to a column of type INTEGER, and that is not allowed.
使用INSERT INTO ... SELECT您可以从一个表中选择行并将它们插入到其他表中。它对您不起作用的原因是因为您试图将字符串值分配给':pos'类型为 的列INTEGER，这是不允许的。
Assuming I have another table MyOtherTablewith a similar structure as MyTableI can transfer values using:
假设我有另一个MyOtherTable具有类似结构的表，因为MyTable我可以使用以下方法传输值：
INSERT INTO MyTable (SomeValue)
SELECT SomeOtherValue
FROM MyOtherTable
Using INSERT INTO ... SELECTit is not possible to obtain the generated values unless only a single row was inserted.
INSERT INTO ... SELECT除非仅插入一行，否则无法使用它获取生成的值。
Guesswork with regard to POSID
猜测有关 POSID
It is not clear to me what POSIDis supposed to be, and what values it should have. It looks like you want to have an increasing value starting at 1 for a single INSERT INTO ... SELECT. In versions of Firebird up to 2.5 that is not possible in this way (in Firebird 3 you would be able to use ROW_NUMBER()for this).
我不清楚POSID应该是什么，它应该具有什么价值。看起来您希望单个INSERT INTO ... SELECT. 在高达 2.5 的 Firebird 版本中，这是不可能的（在 Firebird 3 中，您可以使用ROW_NUMBER()此方式）。
If my guess is right, then you will need to use an EXECUTE BLOCK(or a stored procedure) to assign and increase the value for every row to be inserted.
如果我的猜测是正确的，那么您将需要使用EXECUTE BLOCK（或存储过程）为要插入的每一行分配和增加值。
The execute block would be something like:
执行块将类似于：
EXECUTE BLOCK
AS
DECLARE posid INTEGER = 1;
DECLARE someothervalue VARCHAR(255);
BEGIN
FOR SELECT SomeOtherValue FROM MyOtherTable INTO :someothervalue DO
BEGIN
INSERT INTO MyTable (SomeValue, posid) VALUES (:someothervalue, :posid);
posid = posid + 1;
END
END
Without an ORDER BYwith the SELECTthe value of posid is essentially meaningless, because there is no guaranteed order.
没有一个ORDER BYwith SELECTposid 的值本质上是没有意义的，因为没有保证的顺序。
