CODEWITHSUNDEEP
sqlsql-server
user1063145
user1063145

Reputation: 71

how do i write a single query to insert the data

iam trying insert rows into a sql table in java. i need to insert into a table simulateneously with 2 different data.here am using 2 queries to insert data to dbo.Company obtained from some manipulations into a single table

    statement1.executeUpdate("insert into   
    dbo.Company(CName,DateTimeCreated,DateTimeLastModified) 
    values('"+cname[i]+"','"+ts+"','"+ts+"')");

    statement3.executeUpdate("insert into 
    dbo.Company(CName,DateTimeCreated,DateTimeLastModified) 
    values('"+EMpname[i]+"' ,'"+ts+"','"+ts+"')");

i need to insert cname[i] and EMpname[i] into dbo.Company using single query... please help to write this query.

Upvotes: 2

Views: 154

Answers (4)

mrab
mrab

Reputation: 2728

I would recommend using a transaction.

Transaction tx = session.beginTransaction();
statement1.executeUpdate("insert into   
dbo.Company(CName,DateTimeCreated,DateTimeLastModified) 
values('"+cname[i]+"','"+ts+"','"+ts+"')");

statement3.executeUpdate("insert into 
dbo.Company(CName,DateTimeCreated,DateTimeLastModified) 
values('"+EMpname[i]+"' ,'"+ts+"','"+ts+"')");
tx.commit();

This ensures that either all statements are successfully executed or none of them (rollback). I would also consider using parametrized queries to avoid SQL injections: https://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java#Prepared_Statements

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21776

Use insert statement in form:

INSERT Table(fields)
VALUES 
  (set1), 
  (set2),
  ...,
  (setN)

Your case is:

statement1.executeUpdate("insert into   
dbo.Company(CName,DateTimeCreated,DateTimeLastModified) 
values('"+cname[i]+"','"+ts+"','"+ts+"'),
('"+EMpname[i]+"' ,'"+ts+"','"+ts+"')");

Upvotes: 1

Suzan Cioc
Suzan Cioc

Reputation: 30147

Multiple VALUES supported since 2008. If your server version is lower use UNION: http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

Upvotes: 1

Luca Davanzo
Luca Davanzo

Reputation: 21528

String query = "insert into   
dbo.Company(CName,DateTimeCreated,DateTimeLastModified) 
values('"+cname[i]+"','"+ts+"','"+ts+"');" + "insert into 
dbo.Company(CName,DateTimeCreated,DateTimeLastModified) 
values('"+EMpname[i]+"' ,'"+ts+"','"+ts+"')";

statement1.executeUpdate(query);

Upvotes: 1

Related Questions