Click here to Skip to main content
14,392,909 members
Rate this:
Please Sign up or sign in to vote.
Iam able to insert multiple values (columns) in multiple tales in multiple query. and taking time. to insert



for example
table 1
unique_no name age address
127890 rahul 27 newyork......

table 2
s_no unique_no phone_no car_details salary
45 127890 1234567890 BMW 64,000$

i have insert both tables at one query.

What I have tried:

i have tried

insert into table_1(unique_no, name ,age, address) value('127890','rahul','27', 'newyork......');

insert into table_2(s_no,unique_no,phone_no,car_details,salary) value('45','127890', '1234567890','BMW','64,000$');

it take time, if i have 100 tables to insert columns...
i want to write one query.. is it possible.
Posted
Updated 15-Dec-18 20:42pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Presumably, you are doing this from a presentation language of some form, we have no idea what, or how! Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with. And there are hundreds of different ways you could be doing this.

So start by looking at how you are doing it at the moment: assuming this is a .NET language, use the Stopwatch class[^] to time what you have at present:
Stopwatch sw = new Stopwatch();
sw.Start();
... code to do the timed operation ..
sw.Stop();
Console.WriteLIne(sw.Ticks);
This gives you a baseline to work from, and multiple stopwatches can be used to find out where you code is slow - so you can focus your efforts on speeding that up.
Do note that you may want to do the operations multiple times (several thousand) to get meaningful numbers unless you code is so slow a sundial could time it!

Until you have meaningful stats on how slow it is and where it is slow, it's pointless trying to speed anything up!
   
Comments
kiran0624 15-Dec-18 4:40am
   
i am working in spring and hibernate. there no problem in my java code as quick as quick result.
problem in SQL query.
we are using sql select alias to quick access from thousand of tables getting required columns in one query.
As same can we insert or update columns in 100 table in one query.
Rate this:
Please Sign up or sign in to vote.

Solution 2

For Bulk updates/Inserts/Deletes, you should be using Transactions.
   
v2
Comments
kiran0624 16-Dec-18 1:14am
   
please give me an example, that could be help me!!!
Rate this:
Please Sign up or sign in to vote.

Solution 3

i will explain very clearly my question!!!

public List getECreasdetails(){


SQLQuery reasonDetails = null;
1)String sndreasonDetailsQuery = "INSERT INTO reason_save_tbl_1(main_no, res_save, r_status) "
+ "values('"+main_no+"','"+reasonList.get(i)+"','"+change_int_status+"')";

2)SQLQuery reasonDetails2 = null;
String sndreasonDetailsQuery2 = "INSERT INTO reason_save_tbl_2(main_no, res_save, r_status) "
+ "values('"+main_no+"','"+reasonList.get(i)+"','"+change_int_status+"')";

3)........
4).......
---
---
---
100)SQLQuery reasonDetails100 = null;
String sndreasonDetailsQuery100 = "INSERT INTO reason_save_tbl_100(main_no, res_save, r_status) "
+ "values('"+main_no+"','"+reasonList.get(i)+"','"+change_int_status+"')";

return SUCCESS;
}

if insert each table by table
one query will take 0.05sec
100 query will take 0.05sec*100 thats more than 5mins
if i could run one insert query for 100 table thats will be 0.04sec.
its possible to execute one query like how we are using alias in select query.
   
v3
Comments
Graeme_Grant 17-Dec-18 6:00am
   
Begin, Commit & Rollback ... all transaction keywords that you need to learn... Once you do, you will see the light!
Richard Deeming 18-Dec-18 12:28pm
   
If you want to update your question, then click the green "Improve question" link and edit your question. DO NOT post your update as a "solution".

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100