Jump to content

Inserting identical data to 2 tables efficiently


kickstart

Recommended Posts

Hi

 

Minor and strange idea, and something I am a bit lumbered with due to some fairly nasty existing tables I am working with.

 

I have a fairly complex piece of SQL which unfortunately depends on some data derived elsewhere (a list of users, but with the ids manipulated). I have generated this list of users and put them into a temp table.

 

Unfortunately my main piece of SQL needs to join onto this table of data twice (once for each of a pair of unioned sub queries). MySQL will not allow a temp table to be used more than once in a single query, hence I have just created 2 identical temp tables and populated them twice.

 

This works fine but I am wondering if there are any efficient tricks to populate the data into 2 tables. At the moment I am just executing each of the INSERT statements twice (there could be multiple insert statements to each table - I am adding 1000 rows at a time). This isn't too bad. I did try inserting to the first table conventionally, and then inserting the the seconds using a SELECT of the first table for the values but this appeared to be slower.

 

Any ideas (short of rewriting a large system to eliminate the derivation of the manipulated ids, which I would dearly love to do but can't justify the massive amount of work it would entail)?

 

All the best

 

Keith

Link to comment
Share on other sites

Are you saying creating two temporary tables and joining them onto a query is faster than using something like

 

INSERT INTO tbl1 (Col1, Col2, Col3) SELECT (Col1, Col2, Col3) FROM tbl2

 

I'm shocked if joining two temporary tables is faster than that.

Link to comment
Share on other sites

the table need to be temporary?

 

Possible I could create a single non temp table, although I am a little averse to creating temporary things on a live database.

 

insert 1 time

 

then

 

create temporary table myTemp2 select * from myTemp1;

 

This will then duplicate the table, BUT it won't create any keys you have on the temp table.

 

Interesting, hadn't thought of being able to do the create from a select. Thank you.

 

Are you saying creating two temporary tables and joining them onto a query is faster than using something like

 

INSERT INTO tbl1 (Col1, Col2, Col3) SELECT (Col1, Col2, Col3) FROM tbl2

 

I'm shocked if joining two temporary tables is faster than that.

 

Sorry, I possibly mislead you. I needed 2 copies of the same temp table (used in different subselects of a single large select). Populating them both using a conventional insert statements was faster than populating one and then populating the other using INSERT...... SELECT.... syntax.

 

All the best

 

Keith

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.