CODEWITHSUNDEEP
sqlstored-proceduresoracle10g
Khushal Rajawat
Khushal Rajawat

Reputation: 53

Insert 1000 rows in single sql statment or procedure

How should i write a single sql statement or a stored procedure,

To insert 1000 values in 1000 rows and same column with each column having different values (among those 1000)

Here is the query i wrote,

INSERT INTO a_b values
(
  (SELECT max(a_b_id) + 1 from a_b),
  1111,
  (SELECT s_id FROM a_b WHERE s_id in ('0','1','2','3','4')),
  0,
  1,
  sysdate,
  sysdate,
  0,
  1,
  null
);

like say, i have 1000 s_id's i want select them one by one and insert them in one particular column, each time creating a new row.

EX, in first row s_id should be 0 then in second row it should be 1 like that goes on till thousand, attached an image of sample database i am working with.

Upvotes: 1

Views: 4540

Answers (3)

user330315
user330315

Reputation:

You can use connect by for this:

INSERT INTO a_b  (s_id, col2, col3, ....)
select level, --<< this value will change for every row
       1111,
       sysdate,
       ... more columns ...
from dual
connect by level <= 1000;

Upvotes: 2

Drew
Drew

Reputation: 24959

The below is a syntax error. You will never get something like that to work.

create table fff
(   id int not null
);

insert fff values (select 1,7777,select 3, select 3);

So you need to break it up into chunks

DROP PROCEDURE IF EXISTS uspRunMe;
DELIMITER $$
CREATE PROCEDURE uspRunMe()
BEGIN
    insert into a_b select max(a_b_id) + 1 from a_b;
    insert into a_b values (1111);
    insert into a_b SELECT s_id FROM a_b WHERE s_id in ('0','1','2','3','4');
    insert into a_b values (0,1);
    insert into a_b select sysdate,sysdate;
    insert into a_b values (0,1,null);
END;$$
DELIMITER ;

Test it:

call uspRunMe();

The above is for MySQL. You have a few db engines tagged here.

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

you can use cross apply to get 1000 rows along with 1000 other columns to insert 1000 rows as below:

insert into a_b (column names...)
    select (max(a_b_id) over()) +1 as MaxId, s_id from a_b a cross apply (select 0, 1,SYSDATETIME, SYSDATETIME, 0, 1, null) b where a.s_id('111','222')--condition

Upvotes: 1

Related Questions