SQL INSERT with loop with multiple rows

Question

I'm trying to insert skeleton data into a database using jdbc.

So far my code is:

   Statement st=con.createStatement();
   String sql = "INSERT INTO student (studentid, titleid, forename, familyname, dateofbirth) "
                    + "VALUES (1, 1, 'forename1', 'surname1', '1996-06-03');";

I need to create 100 entries for this and I'm not too sure how to go about doing it. All I want is the student id, titleid, forename and familyname to increment by 1 until it reaches 100 entries with those rows filled in, date of birth doesn't need to be altered. I'm asking how to do a loop for this

how do you create the entries . please explain your problem more clearly — Santhosh, Oct 29 '14 at 12:34
You have an 'edit' button under the question. You can edit and add the code that you forgot and all the other information. — RealSkeptic, Oct 29 '14 at 12:38
You need to add duplicates of the first row 99 times right..? Can't understand your problem claerly.. Need more explanation.. — user562, Oct 29 '14 at 12:39

score 2 · Accepted Answer · edited May 23 '17 at 12:05

2

General answer - You should use PrepareStatement instead of Statement and execute as batch.

Common way to insert multiple entry or execute

String sql = "INSERT INTO student (studentid, titleid, forename, familyname, dateofbirth) "
                    + "VALUES (?, ?, ?, ?, ?);";
ps = connection.prepareStatement(SQL_INSERT);
for (int i = 0; i < entities.size(); i++) {
    ps.setString(1, entity.get...());
    ...
    ps.addBatch();
}
ps.executeBatch();

Important Note:

edited May 23 '17 at 12:05

Community

1
1

answered Oct 29 '14 at 12:41

Subhrajyoti Majumder

40,646
13
77
103

Ok thanks and how do I use setDate to increment by variable i each time? – Alexander Oct 30 '14 at 03:26
`setDate` function accept `java.util.Date`, and [find How can I increment a date by one day in Java?](http://stackoverflow.com/questions/428918/how-can-i-increment-a-date-by-one-day-in-java) – Subhrajyoti Majumder Oct 30 '14 at 05:09

score 0 · Answer 2 · answered Oct 29 '14 at 13:30

There are two ways to do this. You can put your insert query inside a loop or you can put your loop inside an insert query. I have found that the better method depends on the database engine (but I've never used postresql) and the number of records you want to insert. You could bump up against the maximun query length or number of parameters or something.

The following code sample is ColdFusion, but it is intended to show the general idea of having a loop inside a query. You would have to write equivalent code in java.

<cfquery>
insert into yourtable
(field1
, field2
, etc)
select null
, null
, null
from SomeSmalllTable
where 1 = 2
<cfloop>
union
select <cfqueryparam value="#ValueForField1#">
, <cfqueryparam value="#ValueForField#">
, etc
</cfloop>
</cfquery>

SQL INSERT with loop with multiple rows

2 Answers2