insert select 时能否代入变量??(carpy贴)
数据库表一(table_new)有字段：name char(10),sex char(10),age int(),comment nchar(20)
数据库表二(table_old)有字段：name char(10),sex char(10),age int()
也就是表一比表二多一个字段，我现想在代码中实现，将满足条件的表二中的值插入到表一中去，同时在数据的后面插入一个说明。代码如下：
string ls_comment
ls_comment= '与表二相同 '
insert into table_new(name,sex,age,comment)
select *, "+ls_comment+ "
from table_old
where sex= '男 ';
这是否能实现？
如果select *, "+ls_comment+ "这句直接写成：select *, "与表二相同 ".那么程序将不会有问题，我现在想让一个变量来取代它，能否实现？
请各位高手指教.
[解决办法]
应该是可以的
[解决办法]
用动态SQL一可以实现
[解决办法]
insert into table_new(name,sex,age,comment)
select *, '与表二相同 '
from table_old
where sex= '男 '
[解决办法]
string ls_comment
ls_comment= '与表二相同 '
insert into table_new(name,sex,age,comment)
select name,sex,age,comment,:ls_comment
from table_old
where sex= '男 ';
[解决办法]
写错了，多了一个字段
修改为：
string ls_comment
ls_comment= '与表二相同 '
insert into table_new(name,sex,age,comment)
select name,sex,age,:ls_comment
from table_old
where sex= '男 ';
[解决办法]
string ls_comment
String ls_sql
ls_comment= '与表二相同 '
ls_sql= "insert into table_new(name,sex,age,comment) select *, "+ls_comment+ " from table_old where sex= '男 ' "
execute immediate :sql;
[解决办法]
string ls_comment
String ls_sql
ls_comment= '与表二相同 '
ls_sql= "insert into table_new(name,sex,age,comment) select *, "+ls_comment+ " from table_old where sex= '男 ' "
execute immediate :ls_sql;
[解决办法]
再用ls_sql= "insert into table_new(name,sex,age,comment) select *, ' "+ls_comment+ " ' from table_old where sex= '男 ' " 试试，
不行的话干脆写成
ls_sql= "insert into table_new(name,sex,age,comment) select name,sex,age, ' "+ls_comment+ " ' from table_old where sex= '男 ' "
[解决办法]
insert into table_new select * from table_old where (table_old的条件) ;
commit;
update table_new set comment = '与表二相同 ';
commit;
