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
– Santhosh, Commented Oct 29, 2014 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
– RealSkeptic, Commented Oct 29, 2014 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
– user562, Commented Oct 29, 2014 at 12:39

Community · Accepted Answer · 2017-05-23 12:05:38Z

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, 2017 at 12:05

CommunityBot

11 silver badge

answered Oct 29, 2014 at 12:41

Subhrajyoti Majumder

41.3k13 gold badges84 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexander Over a year ago

Ok thanks and how do I use setDate to increment by variable i each time?

Subhrajyoti Majumder Over a year ago

setDate function accept java.util.Date, and find How can I increment a date by one day in Java?

Dan Bracuk · Accepted Answer · 2014-10-29 13:30:13Z

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>

Collectives™ on Stack Overflow

SQL INSERT with loop with multiple rows

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related