用这条语句
insert into table2(USERID,Name,Accounts,MobileNo) select seq_p.nextval,Name,MobileNo,MobileNo from table1;
insert into table2(USERID,Name,Accounts,MobileNo) select seq_p.nextval,Name,MobileNo,MobileNo from table1;
解决方案 »
- 求ORACLE数据备份工具
- oracle通过透明网关访问sybase时,为什么不能增,删,改呢,只能select
- 为什么建议把oracle的表与索引分开存放在不同的表空间?
- 几个相关表的连接查询 ,看懂的话你的sql水平可以给80分了
- 出现ORA-01555错误,有什么办法解决
- oracle9的回闪技术怎么用?我听说oracle9的回闪技术可以把数据恢复到任何时间点上,是这样的吗?怎么用啊?
- 关于ORACLE的数据导入问题~
- 我在那里可以看到我建立的触发器?
- 急!动态sql的运行问题 在线等待给分
- 在oracle9i中,创建一个库后,怎样在库中创建表?
- 数据表
- 刚刚开始学习oracle 请高手们 推荐几本基础书。
The following error has occurred:ORA-00911: 无效字符
所以最好能保证table1(id)和table2(userid)能够有一个对应关系。
可以采用楼上的那种方法,但是还有更好的方法(前提是id和userid有对应关系)
采用merge命令
merge into table2 t2
using table1 t1
on(t2.useid=t1.id)
when matched on
update set .....
when not matched on
insert values(.......);
!
insert into table2 (USERID,Name,ACCOUNTS,MobileNo)
select seq_p.nextval,substr(Name,1,30),
substr(MobileNo,1,50),substr(MobileNo,1,11)
from table1;
Create Procedure SPGenInsertSQL
@TableName Varchar(256) ,
@Where nVarchar(1000) = ''
As
Begin
-- Print @TableName , @Where
Declare @Sql varchar(8000)
Declare @SqlValues varchar(8000)
Set @Sql =' ('
Set @SqlValues = 'values (''+' Select @SqlValues = @SqlValues + Cols + ' + '','' + ' ,@Sql = @Sql + '[' + Name + '],'
From
(Select Case
When xtype in (48,52,56,59,60,62,104,106,108,122,127)
Then 'Case When '+ Name +' Is Null Then ''NULL'' Else ' + 'Cast('+ Name + ' As Varchar)'+' End'
When xtype in (58,61)
Then 'Case When '+ Name +' Is Null Then ''NULL'' Else '+''''''''' + ' + 'Cast('+ Name +' As Varchar)'+ '+'''''''''+' End'
When xtype in (167)
Then 'Case When '+ Name +' Is Null Then ''NULL'' Else '+''''''''' + ' + 'Replace('+ Name+','''''''','''''''''''')' + '+'''''''''+' End'
When xtype in (231)
Then 'Case When '+ Name +' Is Null Then ''NULL'' Else '+'''N'''''' + ' + 'Replace('+ Name+','''''''','''''''''''')' + '+'''''''''+' End'
When xtype in (175)
Then 'Case When '+ Name +' Is Null Then ''NULL'' Else '+''''''''' + ' + 'Cast(Replace('+ Name+','''''''','''''''''''') As Char(' + Cast(length As Varchar) + '))+'''''''''+' End'
When xtype in (239)
Then 'Case When '+ Name +' Is Null Then ''NULL'' Else '+'''N'''''' + ' + 'Cast(Replace('+ Name+','''''''','''''''''''') As Char(' + Cast(length As Varchar) + '))+'''''''''+' End'
Else '''NULL'''
End As Cols,Name
From SysColumns
Where Id = Object_Id(@TableName) ) T Set @Sql ='Select ''INSERT INTO ['+ @TableName + ']' + Left(@Sql,Len(@Sql) - 1) + ') ' + Left(@SqlValues,Len(@SqlValues) - 4) + ')'' From '+ @TableName + '' + ' ' + @Where + ''
-- Print @Sql
Exec (@Sql)
End