怎么一次插入多条

[yushaoxi]

我今天在ORA里面用下列：都有错误，但是一次一条就没有问题，请问是什么问题
INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( '0001', 'aa', '0001', 'aa', '0100', NULL, NULL, NULL, 1, 0, '5000', NULL, NULL, 100 );
INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( '0002', 'bb', '0002', 'bb', '0100', NULL, NULL, NULL, 1, 1, '5000', NULL, NULL, 100 );
INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( '0003', 'cc', '0003', 'cc', '0100', NULL, NULL, NULL, 1, 2, '5000', NULL, NULL, 100 );

[wupangzi]

这当然做不到了！
如果是从其他表里的数据的话才可以做到一次插入多条数据！

[dinya2003]

看SQL基础.

[yushaoxi]

各位大哥，到底是怎么写，我去看看

[lzj033]

用子查询才可以一次插入多条
insert into table1 (col1,col2,...)
select col1,col2 from table2;

[dlmaomao]

还是借助工具PL/SQL DEVELOPER吧，插入数据就像使用EXCEL表格，很方便的。

[skystar99047]

语句不是这样用的！:)

[yushaoxi]

理解错误，
工具：Embarcadero DBArtisan 7.1.0
处理方式没有错误，不过是当时我没有用COMMIT提交，导致没有提交到表

谢谢各位

[ORARichard]

insert into table(...) select '0001', 'aa', '0001', 'aa', '0100', NULL, NULL, NULL, 1, 0, '5000', NULL, NULL, 100 from dual
union
select '0002', 'bb', '0002', 'bb', '0100', NULL, NULL, NULL, 1, 1, '5000', NULL, NULL, 100 from dual
union
select '0003', 'cc', '0003', 'cc', '0100', NULL, NULL, NULL, 1, 2, '5000', NULL, NULL, 100 from dual

[ncowboy]

楼主，我前次也发了一个帖子。我仿佛觉得某个数据库可用这样用多个values。你以前在哪儿用过这样的数据库啊》

[dlmaomao]

搂主也可以将那些插入语句写在一个.sql文件中，然后再plus下执行它。如：
SQL> edit test.sql
粘贴以上的插入语句，别忘了最后一条加上语句：commit;
保存关闭test.sql文件。
SQL> @test
执行test.sql文件内容。
SQL>select * from ..... 查看结果了。

[GerryYang]

insert into table(...) select '0001', 'aa', '0001', 'aa', '0100', NULL, NULL, NULL, 1, 0, '5000', NULL, NULL, 100 from dual
union
select '0002', 'bb', '0002', 'bb', '0100', NULL, NULL, NULL, 1, 1, '5000', NULL, NULL, 100 from dual
union
select '0003', 'cc', '0003', 'cc', '0100', NULL, NULL, NULL, 1, 2, '5000', NULL, NULL, 100 from dual

[liuyi8903]

可以用循环嘛!

[YuriOU]

在命令窗口中可以实现：（在SQL窗口中不行）

INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( '0001', 'aa', '0001', 'aa', '0100', NULL, NULL, NULL, 1, 0, '5000', NULL, NULL, 100 );

INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( '0002', 'bb', '0002', 'bb', '0100', NULL, NULL, NULL, 1, 1, '5000', NULL, NULL, 100 );

INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( '0003', 'cc', '0003', 'cc', '0100', NULL, NULL, NULL, 1, 2, '5000', NULL, NULL, 100 );

commit;

[YuriOU]

或者在SQL窗口中

execute immediate ('
INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( ''0001'', ''aa'', ''0001'', ''aa'', ''0100'', NULL, NULL, NULL, 1, 0, ''5000'', NULL, NULL, 100 );
INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( ''0002'', ''bb'', ''0002'', ''bb'', ''0100'', NULL, NULL, NULL, 1, 1, ''5000'', NULL, NULL, 100 );
INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( ''0003'', ''cc'', ''0003'', ''cc'', ''0100'', NULL, NULL, NULL, 1, 2, ''5000'', NULL, NULL, 100 );')

commit;

[jbas]

begin

INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( '0001', 'aa', '0001', 'aa', '0100', NULL, NULL, NULL, 1, 0, '5000', NULL, NULL, 100 );
INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( '0002', 'bb', '0002', 'bb', '0100', NULL, NULL, NULL, 1, 1, '5000', NULL, NULL, 100 );
INSERT INTO YUSX.Users ( UserID, UserName, LoginName, UserAlias, DeptID, DutyID, Password, PasswordTerm, CanLogin, OrderIndex, UserRank, Sex, UserDuty, Quotiety )
VALUES ( '0003', 'cc', '0003', 'cc', '0100', NULL, NULL, NULL, 1, 2, '5000', NULL, NULL, 100 );

end;

加上begin ...end;应该就可以了