I work on Firebird 2.5 and I have two tables, all their columns are similar except one has a primary key with auto increment and a not null foreign key field (A) for master table
我在Firebird 2.5中工作,我有两个表,它们的所有列都是相似的,除了一个具有自动增量的主键和一个主表的非null外键字段(a)。
I know I can use sql like this to insert all values from the two tables
我知道我可以像这样使用sql来插入两个表中的所有值
insert into table1 select * from table2 where somthing = 'foo'
but what about the field (A) is there any way to insert this value manually in the same sql statement ? as this is the only field need to be entered manually
但是对于字段(A)有什么方法可以在相同的sql语句中手动插入这个值吗?因为这是唯一需要手动输入的字段
Thanks
谢谢
2 个解决方案
#1
5
You can specify both the source and target fields explicitly (and you should; don't use select *
unless you have a specific reason to):
可以显式地指定源字段和目标字段(应该这样做;不要使用select *除非你有特定的理由):
insert into table1
(
col1,
col2,
col3,
col4
)
select
col1,
col2,
col3,
'foo'
from table2
where something = 'foo'
#2
2
Came upon this post because I was looking for a solution to do the same, but without hard-coding the field names because the fields maybe added/removed and didn't want to have to remember to update the copy record procedure.
找到这篇文章是因为我正在寻找一种解决方案来完成同样的工作,但是没有硬编码字段名,因为字段可能被添加/删除,并且不希望忘记更新复制记录过程。
After googling around for awhile, I came up with this solution:
在搜索了一段时间之后,我想到了这个解决方案:
select cast(list(trim(RDB$FIELD_NAME)) as varchar(10000))
from RDB$RELATION_FIELDS
where RDB$RELATION_NAME = 'YOUR_TABLE'
and RDB$FIELD_NAME not in ('ID') -- include other fields to NOT copy
into :FIELD_NAMES;
NEW_ID = next value for YOUR_TABLE_ID_GENERATOR;
execute statement '
insert into YOUR_TABLE (ID,' || FIELD_NAMES || ')
select ' || cast(:NEW_ID as varchar(20)) || ',' ||
FIELD_NAMES || '
from YOUR_TABLE
where ID = ' || cast(:ID_OF_RECORD_TO_COPY as varchar(20));
Hope this saves some time for anyone else who comes across this issue!
希望这能为遇到这个问题的人节省一些时间!
#1
5
You can specify both the source and target fields explicitly (and you should; don't use select *
unless you have a specific reason to):
可以显式地指定源字段和目标字段(应该这样做;不要使用select *除非你有特定的理由):
insert into table1
(
col1,
col2,
col3,
col4
)
select
col1,
col2,
col3,
'foo'
from table2
where something = 'foo'
#2
2
Came upon this post because I was looking for a solution to do the same, but without hard-coding the field names because the fields maybe added/removed and didn't want to have to remember to update the copy record procedure.
找到这篇文章是因为我正在寻找一种解决方案来完成同样的工作,但是没有硬编码字段名,因为字段可能被添加/删除,并且不希望忘记更新复制记录过程。
After googling around for awhile, I came up with this solution:
在搜索了一段时间之后,我想到了这个解决方案:
select cast(list(trim(RDB$FIELD_NAME)) as varchar(10000))
from RDB$RELATION_FIELDS
where RDB$RELATION_NAME = 'YOUR_TABLE'
and RDB$FIELD_NAME not in ('ID') -- include other fields to NOT copy
into :FIELD_NAMES;
NEW_ID = next value for YOUR_TABLE_ID_GENERATOR;
execute statement '
insert into YOUR_TABLE (ID,' || FIELD_NAMES || ')
select ' || cast(:NEW_ID as varchar(20)) || ',' ||
FIELD_NAMES || '
from YOUR_TABLE
where ID = ' || cast(:ID_OF_RECORD_TO_COPY as varchar(20));
Hope this saves some time for anyone else who comes across this issue!
希望这能为遇到这个问题的人节省一些时间!